关于数据库: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';

写完SQL，记录备用。