-
Index rebuild on exchange partition
Dear All,
I have one partition table and one non partition table. I am exchanging partition table into non partition table. When i exchange, i am using UPDATE GLOBAL INDEX key word. It rebuilds the index for partition table. But still the non partition table index become UNUSABLE. I am manually rebuilding this non partition table index for every parition exchange. I wanted to rebuild the non partition table index during my partition exchange. Is it possible? Please advise.
SQL>
SQL> CREATE TABLE sourcetable
2 (OBJECT_NAME VARCHAR2(100))
3 PARTITION BY RANGE (OBJECT_NAME)
4 (PARTITION P1 VALUES LESS THAN (MAXVALUE))
5 /
Table created.
SQL>
SQL> CREATE UNIQUE INDEX SOURCETABLE_IDX ON sourcetable
2 (OBJECT_NAME)
3 /
Index created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE DESTTABLE
2 (OBJECT_NAME VARCHAR2(100))
3 /
Table created.
SQL>
SQL> CREATE UNIQUE INDEX DESTTABLE_IDX ON desttable
2 (OBJECT_NAME)
3 /
Index created.
SQL>
SQL> insert into sourcetable select object_name from user_objects
2 where rownum < 5;
4 rows created.
SQL>
SQL> select index_name,status from user_indexes
2 where table_name in('SOURCETABLE','DESTTABLE')
3 /
INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX VALID
SOURCETABLE_IDX VALID
SQL>
SQL> ALTER TABLE sourcetable EXCHANGE PARTITION p1 WITH TABLE
2 DESTTABLE update global indexes
3 /
Table altered.
SQL>
SQL> select index_name,status from user_indexes
2 where table_name in('SOURCETABLE','DESTTABLE')
3 /
INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX UNUSABLE
SOURCETABLE_IDX VALID
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|