ALTER INDEX SH.SALES_IDX4 REBUILD PARTITION SYS_P51490 TABLESPACE DATA04 ONLINE;Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index). To generate rebuild-statements, one index at a time, I have used the script below. It takes as arguments
- The index owner
- The index name
- The new tablespace where you want to place your partitions
- The old tablespace from which you want to move out
set termout off select 'alter session set nls_language=''american'';' from dual; set termout on accept index_owner prompt 'Index owner: ' accept index_name prompt 'Index name: ' accept new_tabspc prompt 'New tablespace: ' accept old_tabspc prompt 'Old tablespace: ' set lines 300 set heading off set feedback off set verify off set echo off set pages 0 set trimspool on spool exec_move_part_&&index_owner..&&index_name..sql select 'alter session set nls_language=''american'';' from dual; select 'alter session force parallel ddl;' from dual; select 'set timing on' from dual; select 'set lines 200' from dual; select 'set trimspool on' from dual; select 'spool exec_rebuild_part_&&index_owner..&&index_name..log' from dual; select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_part_&&index_name'');' from dual; select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_part_t_&&index_name'');' from dual; select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_part_&&index_name''); ' from dual; select 'prompt moving the index partitions from &&old_tabspc to &&new_tabspc,' from dual; select 'prompt and setting default attributes for the table ' from dual; SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE &&new_tabspc ONLINE;' FROM dba_indexes idx, dba_ind_partitions idxpart WHERE idx.table_owner = '&&index_owner' and idxpart.TABLESPACE_NAME = '&&old_tabspc' AND idx.index_name = idxpart.index_name AND idx.index_name = '&&index_name' AND idxpart.subpartition_count=0 ORDER BY idx.table_owner, idx.index_name; select distinct 'alter index ' || IDXPART.INDEX_OWNER || '.' || IDXPART.INDEX_NAME || ' modify default attributes tablespace &&new_tabspc;' FROM DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME ) WHERE IDXPART.INDEX_NAME='&&index_name' AND IDXPART.TABLESPACE_NAME='&&old_tabspc'; select 'exit' from dual; exitRun it as a user with access to the data dictionary:
sqlplus / as sysdba @gen_move_idx_part.sql
It will generate a new script called exec_move_part_<username>.<index_name>.sql which will perform the actual rebuild:
sqlplus / as sysdba @exec_move_part_<username>.<index_name>.sql
I also added som calls to dbms_application_info so that the session can be easily identfied in v$session. Remember, sometimes it's desirable to set the PARALLEL degree and the NOLOGGING options during rebuild, to complete it as fast as possible. See this post for more info.
No comments:
Post a Comment