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

12C版本有些参数有变动，Move表反对Online，不过没生产上尝试。
-- 批量生成move表及rebuild索引语句/* 替换以下属性信息，By Xander.CuiOWNER(SCHEMA NAME): MYUSERTABLE_NAME: MY_TABLETABLESPACE: 沿用当初的表空间（更改请指定）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 ALLSELECT '-- Move 表(不含 LONG 字段) CMD:' AS COMMEND FROM DUALUNION 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 ALLSELECT '-- Move 表(不含 LONG 字段) LOB 列(如果有) CMD:' FROM DUALUNION 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 ALLSELECT '-- Rebuild 索引(索引名不蕴含 "$$" 字符串的索引) CMD:' FROM DUALUNION 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';