Simulate alter table move
-- Create table test
create table subjects (
subject_id varchar2(30) not null,
subject_name varchar2(30)
) tablespace PSAPIDS;
Table created.
-- Insert two record
insert into subjects values ('1','aaaa');
1 row created.
insert into subjects values ('2','bbbb');
1 row created.
commit;
Commit complete.
-- Show rowid two record
select rowid,subject_id,subject_name from subjects;
ROWID SUBJECT_ID SUBJECT_NAME
------------------ ------------------------------ ------------------------------
AACCnCAATAABbx6AAA 1 aaaa
AACCnCAATAABbx6AAB 2 bbbb
-- Create Index for table
CREATE INDEX indx_subject_id ON
subjects(subject_id)
TABLESPACE PSAPIDS;
Index created.
-- Select table & index for table
select TABLE_NAME "table with indexes",
INDEX_NAME,STATUS
from dba_indexes
where table_name like 'SUBJECTS';
table with indexes INDEX_NAME STATUS
------------------------------ ------------------------------ --------
SUBJECTS INDX_SUBJECT_ID VALID
-- Move table to another tablespace
ALTER TABLE subjects MOVE TABLESPACE PSAPIDSUSR;
Table altered.
-- Insert one record but can't insert
insert into subjects values ('3','ccccc');
insert into subjects values ('3','ccccc')
*
ERROR at line 1:
ORA-01502: index 'SYS.INDX_SUBJECT_ID' or partition of such index is in
unusable state
-- Show rowid two record
select rowid,subject_id,subject_name from subjects
/
ROWID SUBJECT_ID SUBJECT_NAME
------------------ ------------------------------ ------------------------------
AACCnMAAeAAAArCAAA 1 aaaa
AACCnMAAeAAAArCAAB 2 bbbb
-- Select table & index for table for show unusable status
select TABLE_NAME "table with indexes",
INDEX_NAME,STATUS
from dba_indexes
where table_name like 'SUBJECTS';
table with indexes INDEX_NAME STATUS
------------------------------ ------------------------------ --------
SUBJECTS INDX_SUBJECT_ID UNUSABLE
-- Rebuild index
ALTER INDEX indx_subject_id REBUILD;
Index altered.
-- Drop table test
drop table subjects;
Table dropped.
1)From Web change "alter table" move command will be change Row ID
Reorganization with "alter table xxx move" syntax
|
Question: Does the "alter table" move command preserve index and constraint definitions?
Answer: Oracle has many ways to reorganize tables, including CTAS, dbms_redefinition, alter table move, and data pump (expdp, impdp).
According to Mike Hordila, the alter table move syntax preserves the index and constraint definitions:
"The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE).
Therefore, the need to execute step two immediately after step one - rebuild the invalid indexes on the current table.
At the same time an advantage of using the table move procedure is all constraints are preserved, and index definitions are also saved, so that reindexing is possible using the fast index REBUILD method, rather than the slower index DROP and CREATE method."
At the same time an advantage of using the table move procedure is all constraints are preserved, and index definitions are also saved, so that reindexing is possible using the fast index REBUILD method, rather than the slower index DROP and CREATE method."
2)From Web change LOB column datatype
FOR LOB DATA TYPE:
The default manual rebuild scripts generated by alter table move will be as below and it fails with below error. So use above SQL for LOB data types. This need to be done only for the index on LOB column and rest of the indexes can be done by manual scripts. So change the script with above sql where is LOB index with appropriate tablename and LOB column name.
alter index AR.SYS_IL0000716146C00221$$ rebuild tablespace ARXS1 storage(initial 128k next 128k pctincrease 0);
ERROR at line 1: RA-02327: cannot create index on expression with datatype LOB
You can get the LOB column details by using the following query:
select table_name, column_name,segment_name from dba_lobs where Index_name='SYS_IL0000716146C00221$$' and owner='AR';
TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- -------------------- ------------------------------
HZ_PARTY_INTERFACE BUSINESS_REPORT SYS_LOB0000716146C00221$$
Syntax:
alter table AR.HZ_PARTY_INTERFACE move lob(BUSINESS_REPORT)
store as lobsegment (tablespace ARDS1 storage(initial 512k next 512k pctincrease 0));NOTE: We have to rebuild other indexes after moving the lobsegment to another tablespace.
The default manual rebuild scripts generated by alter table move will be as below and it fails with below error. So use above SQL for LOB data types. This need to be done only for the index on LOB column and rest of the indexes can be done by manual scripts. So change the script with above sql where is LOB index with appropriate tablename and LOB column name.
alter index AR.SYS_IL0000716146C00221$$ rebuild tablespace ARXS1 storage(initial 128k next 128k pctincrease 0);
ERROR at line 1: RA-02327: cannot create index on expression with datatype LOB
You can get the LOB column details by using the following query:
select table_name, column_name,segment_name from dba_lobs where Index_name='SYS_IL0000716146C00221$$' and owner='AR';
TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- -------------------- ------------------------------
HZ_PARTY_INTERFACE BUSINESS_REPORT SYS_LOB0000716146C00221$$
Syntax:
alter table AR.HZ_PARTY_INTERFACE move lob(BUSINESS_REPORT)
store as lobsegment (tablespace ARDS1 storage(initial 512k next 512k pctincrease 0));NOTE: We have to rebuild other indexes after moving the lobsegment to another tablespace.
0 comments:
Post a Comment