(Oracle 11g) batch generation of move table and rebuild index statement

Time:2021-8-31

Some parameters of 12C version have changed. The move table supports online, but it has not been tried in production.

--Batch generation of move table and rebuild index statement
/*Replace the following attribute information, by xander.cui
OWNER(SCHEMA NAME): A01_GOLDEN
TABLE_NAME: 'SFC_SN_TRAC_PART'
Tablespace: use the current tablespace (specify if you want to change)
Parallel 4: enable parallelism of 4, and allocate according to the specific value of < = CPU cores; Turn on and off parallel
*/
Select '-- move table (without long field) CMD:' as command from dual
UNION ALL
SELECT TC.OWNER||'.'||TC.TABLE_ The name | 'table contains a long type field and does not support move. Do not execute the following move command.'
  FROM DBA_TAB_COLUMNS TC
 WHERE TC.DATA_TYPE = 'LONG'
   AND TC.OWNER = UPPER('A01_GOLDEN')
   AND TC.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
   AND ROWNUM = 1
UNION ALL
--Move common table
SELECT 'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MOVE TABLESPACE '|| T.TABLESPACE_NAME ||' PARALLEL 4;'
       ||CHR(10)||'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' NOPARALLEL;'
  FROM DBA_TABLES T
 WHERE T.OWNER = UPPER('A01_GOLDEN')
   AND T.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
   AND T.PARTITIONED = 'NO'
UNION ALL
--The move partition table already has a partition
SELECT 'ALTER TABLE '||TP.TABLE_OWNER||'.'||TP.TABLE_NAME||' MOVE PARTITION '||TP.PARTITION_NAME||' TABLESPACE '||TP.TABLESPACE_NAME||' PARALLEL 4;'
       ||CHR(10)||'ALTER TABLE '||TP.TABLE_OWNER||'.'||TP.TABLE_NAME||' NOPARALLEL;'
  FROM DBA_TAB_PARTITIONS TP
 WHERE TP.TABLE_OWNER = UPPER('A01_GOLDEN')
   AND TP.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
UNION ALL
Select '-- lob column (if any) of move table (excluding long field) CMD:' from dual
UNION ALL
--Move common table Lob 字段
SELECT 'ALTER TABLE '||A.OWNER||'.'||A.TABLE_NAME||' MOVE LOB('||A.COLUMN_NAME||') STORE AS (TABLESPACE '||A.TABLESPACE_NAME||') PARALLEL 4;'
       ||CHR(10)||'ALTER TABLE '||A.OWNER||'.'||A.TABLE_NAME||' NOPARALLEL;'
  FROM DBA_LOBS A
 WHERE A.PARTITIONED = 'NO'
   AND A.OWNER = UPPER('A01_GOLDEN')
   AND A.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
UNION ALL
--Lob field of move partition table
SELECT 'ALTER TABLE '||TP.TABLE_OWNER||'.'||TP.TABLE_NAME||' MOVE PARTITION '||TP.PARTITION_NAME||' LOB('||C.COLUMN_NAME||') STORE AS ('||'TABLESPACE '||TP.TABLESPACE_NAME||') PARALLEL 4;'
       ||CHR(10)||'ALTER TABLE '||TP.TABLE_OWNER||'.'||TP.TABLE_NAME||' NOPARALLEL;'
  FROM DBA_TAB_PARTITIONS TP
  LEFT JOIN DBA_TAB_COLUMNS C
    ON TP.TABLE_OWNER = C.OWNER
   AND TP.TABLE_NAME = C.TABLE_NAME
 WHERE C.DATA_TYPE LIKE '%LOB'
   AND C.OWNER = UPPER('A01_GOLDEN')
   AND TP.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
UNION ALL
Select '-- rebuild index (index whose index name does not contain "$$" string) CMD:' from dual
UNION ALL
--Rebuild non partitioned index
SELECT 'ALTER INDEX '||I.OWNER||'.'||I.INDEX_NAME||' REBUILD ONLINE TABLESPACE '||I.TABLESPACE_NAME||' PARALLEL 4;'
       ||CHR(10)|| 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME ||' NOPARALLEL;'
  FROM DBA_INDEXES I
 WHERE I.STATUS = 'VALID'
   AND I.OWNER = UPPER('A01_GOLDEN')
   AND I.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
   AND I.INDEX_NAME NOT LIKE '%$$%'
   AND I.PARTITIONED = 'NO'
UNION ALL
--Rebuild partition index
SELECT 'ALTER INDEX '||I.OWNER||'.'||I.INDEX_NAME||' REBUILD ONLINE TABLESPACE '||I.TABLESPACE_NAME||' PARALLEL 4;'
       ||CHR(10)|| 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME ||' NOPARALLEL;'
  FROM DBA_INDEXES I
  LEFT JOIN DBA_IND_PARTITIONS IP
    ON I.OWNER = IP.INDEX_OWNER
   AND I.INDEX_NAME = IP.INDEX_NAME
 WHERE I.OWNER = UPPER('A01_GOLDEN')
   AND I.TABLE_NAME = UPPER('SFC_SN_TRAC_PART')
   AND I.INDEX_NAME NOT LIKE '%$$%'
   AND I.PARTITIONED = 'YES'

After writing SQL, record it for standby.