Move、rebuild index、Move table等操作后检查

-- 检查表并行度
col TURN_OFF_PARALLEL for a75
SELECT T.DEGREE,
       'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' NOPARALLEL;' TURN_OFF_PARALLEL
  FROM DBA_TABLES T
  JOIN DBA_USERS U
    ON T.OWNER = U.USERNAME
   AND U.ACCOUNT_STATUS = 'OPEN'
   AND U.USERNAME NOT IN ('SYS','SYSTEM')
   AND (CASE
         WHEN TRIM(T.DEGREE) = 'DEFAULT' THEN
          0
         ELSE
          TO_NUMBER(TRIM(T.DEGREE))
       END) >= 2;


-- 检查索引并行度
SELECT I.DEGREE,
       'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' NOPARALLEL;' TURN_OFF_PARALLEL
  FROM DBA_INDEXES I
  JOIN DBA_USERS U
    ON I.OWNER = U.USERNAME
   AND U.ACCOUNT_STATUS = 'OPEN'
   AND (CASE
         WHEN TRIM(I.DEGREE) = 'DEFAULT' THEN
          0
         ELSE
          TO_NUMBER(TRIM(I.DEGREE))
       END) >= 2;


-- 检查失效索引
SELECT IP.INDEX_OWNER, IP.INDEX_NAME, IP.PARTITION_NAME, IP.STATUS
  FROM DBA_IND_PARTITIONS IP
  JOIN DBA_USERS U
    ON IP.INDEX_OWNER = U.USERNAME
   AND U.ACCOUNT_STATUS = 'OPEN'
   AND U.USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN')
   AND IP.STATUS = 'UNUSABLE'
UNION ALL
SELECT I.OWNER,
       I.INDEX_NAME,
       'Not Partitioned Index' AS PARTITION_NAME,
       I.STATUS
  FROM DBA_INDEXES I
  JOIN DBA_USERS U
    ON I.OWNER = U.USERNAME
   AND U.ACCOUNT_STATUS = 'OPEN'
   AND I.STATUS = 'UNUSABLE';


-- 检查分区表ROW MOVEMENT 开启情况，不能有 DISABLE
col Enable_movement for a80
SELECT 'ALTER TABLE '||T.OWNER||'.'||T.TABLE_NAME||' ENABLE ROW MOVEMENT;' Enable_movement FROM DBA_TABLES T JOIN DBA_USERS U ON T.OWNER=U.USERNAME AND U.ACCOUNT_STATUS='OPEN' AND U.USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') AND T.PARTITIONED='YES' AND ROW_MOVEMENT = 'DISABLED';

-- 检查索引和表不同用户的索引
SELECT OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED from dba_indexes where OWNER<>TABLE_OWNER;