关于数据库:Oracle-11g批量生成move表及rebuild索引语句

12C 版本有些参数有变动，Move 表反对 Online，不过没生产上尝试。
-- 批量生成 move 表及 rebuild 索引语句
/* 替换以下属性信息，By Xander.Cui
OWNER(SCHEMA NAME): MYUSER
TABLE_NAME: MY_TABLE
TABLESPACE: 沿用当初的表空间（更改请指定）PARALLEL 4: 开启并行度为 4，依照 <= CPU Cores 具体值调配；开启后敞开并行
*/
SELECT TC.OWNER||'.'||TC.TABLE_NAME||'表蕴含 Long 类型字段，不反对 Move，以下 Move 命令请勿执行。'
  FROM DBA_TAB_COLUMNS TC
 WHERE TC.DATA_TYPE = 'LONG'
   AND TC.OWNER = UPPER('MYUSER')
   AND TC.TABLE_NAME IN UPPER('MY_TABLE')
UNION ALL
SELECT '-- Move 表 (不含 LONG 字段) CMD:' AS COMMEND FROM DUAL
UNION ALL
-- Move 一般表
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('MYUSER')
   AND T.TABLE_NAME IN UPPER('MY_TABLE')
   AND T.PARTITIONED = 'NO'
UNION ALL
-- Move 分区表已有分区
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('MYUSER')
   AND TP.TABLE_NAME IN UPPER('MY_TABLE')
UNION ALL
SELECT '-- Move 表 (不含 LONG 字段) LOB 列 (如果有) CMD:' FROM DUAL
UNION ALL
-- Move 一般表 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 IN ('MYUSER')
   AND A.TABLE_NAME IN UPPER('MY_TABLE')
UNION ALL
-- Move 分区表 Lob 字段
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('MYUSER')
   AND TP.TABLE_NAME IN UPPER('MY_TABLE')
UNION ALL
SELECT '-- Rebuild 索引 (索引名不蕴含"$$"字符串的索引) CMD:' FROM DUAL
UNION ALL
-- Rebuild 非分区索引
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('MYUSER')
   AND I.TABLE_NAME IN UPPER('MY_TABLE')
   AND I.INDEX_NAME NOT LIKE '%$$%'
   AND I.PARTITIONED = 'NO'
UNION ALL
-- Rebuild 分区索引
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('MYUSER')
   AND I.TABLE_NAME IN UPPER('MY_TABLE')
   AND I.INDEX_NAME NOT LIKE '%$$%'
   AND I.PARTITIONED = 'YES';