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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/67/viewspace-982554/