set trimspool on set lines 200 set pages 0 set heading off set verify off set feedback off set echo off spool rebuild_stmts.sql --Rebuild the index partition to the same tablespace: SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild partition ' || idxpart.partition_name || ';' FROM dba_indexes idx, dba_ind_partitions idxpart WHERE idx.table_owner = 'SCOTT' AND idx.index_name = idxpart.index_name AND idxpart.status <> 'USABLE' ORDER BY idx.table_owner, idx.index_name / --For a specific INDEX, reallocating the index partitions to a new tablespace: SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;' FROM dba_indexes idx, dba_ind_partitions idxpart WHERE idx.table_owner = 'SCOTT' AND idx.index_name = idxpart.index_name AND idx.index_name = 'IDX_01' AND idxpart.status <> 'USABLE' ORDER BY idx.table_owner, idx.index_name / --Use sqlplus variables, also reallocating the index partitions to a new tablespace: SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE MYTABLESPACE;' FROM dba_indexes idx, dba_ind_partitions idxpart WHERE idx.table_owner = 'SCOTT' AND idx.index_name = idxpart.index_name AND idx.index_name = '&&index_name' AND idxpart.status <> 'USABLE' ORDER BY idx.table_owner, idx.index_name / -- Rebuild index subpartitions. Also add a parallel statement and the ONLINE keyword: SELECT 'alter index ' || IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' parallel 4 tablespace ' || IDXSUBPART.TABLESPACE_NAME ||' online;' FROM DBA_INDEXES IDX, DBA_IND_SUBPARTITIONS IDXSUBPART WHERE IDX.TABLE_OWNER IN ('SCOTT','JAMES') AND IDX.INDEX_NAME = IDXSUBPART.INDEX_NAME AND IDX.INDEX_NAME IN (SELECT UNIQUE INDEX_NAME FROM DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE') AND INDEX_OWNER NOT IN ('SYS','SYSTEM') AND IDXSUBPART.STATUS <> 'USABLE' ORDER BY IDX.OWNER, IDX.INDEX_NAME /Upon completion, the file "rebuild_stmts.sql" should contain your "ALTER INDEX REBUILD PARTITION..." directives.
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Tuesday, January 14, 2014
How to generate a script to rebuild unusable index partitions
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment