best practice of rebuild your partition table local index online by using: "update indexes"

The following operations support the UPDATE INDEXES clause:

• ADD PARTITION | SUBPARTITION

• COALESCE PARTITION | SUBPARTITION

• DROP PARTITION | SUBPARTITION

• EXCHANGE PARTITION | SUBPARTITION

• MERGE PARTITION | SUBPARTITION

• MOVE PARTITION | SUBPARTITION

• SPLIT PARTITION | SUBPARTITION

• TRUNCATE PARTITION | SUBPARTITION

SQL> alter table SAPKBW."/BIC/FZSD_C71" move partition "/BIC/FZSD_C710000000123" tablespace PSAPKBWFACT update indexes;

Table altered.

SQL> set linesize 200
SQL> set pagesize 200
SQL> col table_owner for a10
SQL> col table_name for a15
SQL> col partition_name for a25
SQL> col type for a10
SQL> col column_name for a15
SQL> col sub_type for a10
SQL> col tablespace_name for a15
SQL> select a.index_owner,a.index_name,a.partition_name, b.PARTITIONING_TYPE,
2 b.SUBPARTITIONING_TYPE as sub_type, b.LOCALITY, a.status,a.TABLESPACE_NAME,
3 c.COLUMN_NAME as COLUMN_NAME
4 from sys.dba_ind_partitions a, sys.DBA_PART_INDEXES b, sys.DBA_PART_KEY_COLUMNS c
5 where (a.INDEX_NAME=b.INDEX_name and a.INDEX_owner=b.owner) and (a.INDEX_NAME=c.name and a.index_owner=c.owner)
6 and lower(a.index_name) like lower('%/BIC/FZSD_C71~900%')
7 and a.partition_name like '%/BIC/FZSD_C710000000123'
8 /

INDEX_OWNER INDEX_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------- ------- ---------- ------ -------- --------------- ---------------
SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C710000000123 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P

if you forget, here is the make-up plan:

1.
----------- -------------------- -------------------- -------------------- ------- ---------- ------ -------- --------------- -------------

SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C71 /BIC/FZSD_C710000000 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P
123

2.
SQL> alter table SAPKBW."/BIC/FZSD_C71" move partition "/BIC/FZSD_C710000000123" tablespace PSAPKBWFACT ;

3.
SQL> host kla partidx /BIC/FZSD_C71~900

INDEX_OWNER INDEX_NAME TABLE_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
----------- -------------------- -------------------- -------------------- ------- ---------- ------ -------- --------------- -------------

SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C71 /BIC/FZSD_C710000000 RANGE NONE LOCAL UNUSABLE PSAPKBWFACT KEY_ZSD_C71P
123

4.

SQL> ALTER TABLE sapkbw."/BIC/FZSD_C71"
MODIFY PARTITION "/BIC/FZSD_C710000000123" REBUILD UNUSABLE LOCAL INDEXES; 2

Table altered.

or

SQL> alter index sapkbw."/BIC/FZSD_C71~900" rebuild partition "/BIC/FZSD_C710000000166" PARALLEL (DEGREE 2) online;

Index altered.

5.

SQL> select a.index_owner,a.index_name,a.partition_name, b.PARTITIONING_TYPE,
2 b.SUBPARTITIONING_TYPE as sub_type, b.LOCALITY, a.status,a.TABLESPACE_NAME,
3 c.COLUMN_NAME as COLUMN_NAME
4 from sys.dba_ind_partitions a, sys.DBA_PART_INDEXES b, sys.DBA_PART_KEY_COLUMNS c
5 where (a.INDEX_NAME=b.INDEX_name and a.INDEX_owner=b.owner) and (a.INDEX_NAME=c.name and a.index_owner=c.owner)
6 and lower(a.index_name) like lower('%/BIC/FZSD_C71~900%')
7 and a.partition_name like '%/BIC/FZSD_C710000000123%'
8 /

INDEX_OWNER INDEX_NAME PARTITION_NAME PARTITI SUB_TYPE LOCALI STATUS TABLESPACE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------- ------- ---------- ------ -------- --------------- ---------------
SAPKBW /BIC/FZSD_C71~900 /BIC/FZSD_C710000000123 RANGE NONE LOCAL USABLE PSAPKBWFACT KEY_ZSD_C71P

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67/viewspace-982554/，如需转载，请注明出处，否则将追究法律责任。