Oracle 大索引重组、大表重组

大索引重组

复制查询结果，并执行

SELECT S.BYTES / 1024 / 1024, S.EXTENTS, I.OWNER, I.INDEX_NAME,
'ALTER index ' || I.OWNER || '."' || I.index_name ||
       '" rebuild STORAGE ( INITIAL ' || (CASE
         WHEN S.BYTES / 1024 / 1024 > 1000 THEN
          500
         WHEN S.BYTES / 1024 / 1024 > 500 THEN
          200
         WHEN S.BYTES / 1024 / 1024 > 100 THEN
          50
         WHEN S.BYTES / 1024 / 1024 > 50 THEN
          40
         WHEN S.BYTES / 1024 / 1024 > 10 THEN
          20
         ELSE
          2
       END) || 'M  NEXT ' || (CASE
         WHEN S.BYTES / 1024 / 1024 > 1000 THEN
          200
         WHEN S.BYTES / 1024 / 1024 > 500 THEN
          100
         WHEN S.BYTES / 1024 / 1024 > 100 THEN
          30
         WHEN S.BYTES / 1024 / 1024 > 50 THEN
          20
         WHEN S.BYTES / 1024 / 1024 > 10 THEN
          10
         ELSE
          2
       END) || 'M);' SSQL
  FROM DBA_INDEXES I
  JOIN DBA_SEGMENTS S ON I.OWNER = S.OWNER
                     AND I.INDEX_NAME = S.SEGMENT_NAME
 WHERE I.OWNER LIKE 'GoogleGoogle%'
 AND S.BYTES / 1024 / 1024 > 10
 ORDER BY S.BYTES DESC;

大表重组

SELECT S.BYTES / 1024 / 1024,
       S.EXTENTS,
       T.OWNER,
       T.TABLE_NAME,
       'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME ||
       ' MOVE STORAGE ( INITIAL ' || (CASE
         WHEN S.BYTES / 1024 / 1024 > 1000 THEN
          500
         WHEN S.BYTES / 1024 / 1024 > 500 THEN
          200
         WHEN S.BYTES / 1024 / 1024 > 100 THEN
          50
         WHEN S.BYTES / 1024 / 1024 > 50 THEN
          40
         WHEN S.BYTES / 1024 / 1024 > 10 THEN
          20
         ELSE
          2
       END) || 'M  NEXT ' || (CASE
         WHEN S.BYTES / 1024 / 1024 > 1000 THEN
          200
         WHEN S.BYTES / 1024 / 1024 > 500 THEN
          100
         WHEN S.BYTES / 1024 / 1024 > 100 THEN
          30
         WHEN S.BYTES / 1024 / 1024 > 50 THEN
          20
         WHEN S.BYTES / 1024 / 1024 > 10 THEN
          10
         ELSE
          2
       END) || 'M);' SSQL
  FROM DBA_TABLES T
  JOIN DBA_SEGMENTS S ON T.OWNER = S.OWNER
                     AND T.TABLE_NAME = S.SEGMENT_NAME
 WHERE T.OWNER LIKE 'GoogleGoogle%'
   AND S.BYTES / 1024 / 1024 > 10
 ORDER BY S.BYTES DESC;

SELECT S.BYTES / 1024 / 1024 BYTES_M,
       S.EXTENTS,
       s.initial_extent/1024/1024 initial_extent_M,
       T.OWNER,
       T.TABLE_NAME,
       I.INDEX_NAME,
       'ALTER index ' || I.OWNER || '."' || I.INDEX_NAME || '" rebuild  ;' SSQL
  FROM DBA_TABLES T
  JOIN DBA_SEGMENTS S ON T.OWNER = S.OWNER
                     AND T.TABLE_NAME = S.SEGMENT_NAME
  JOIN DBA_INDEXES I ON T.OWNER = I.OWNER
                    AND T.TABLE_NAME = I.TABLE_NAME
 WHERE T.OWNER LIKE 'GoogleGoogle%'
   AND S.BYTES / 1024 / 1024 > 10
 ORDER BY S.BYTES DESC;

由于是采用的MOVE方式，会导致重组的TABLE索引全部失效，需要先记录下将要的重组的TABLE下的所有索引，等表重组完成后，再执行其索引的重组

注意要先执行以上2个SQL，得到查询结果后，再去执行表重组，防止重组表后索引重组sql查询记录会发生变化，导致的异常情况。（真出现了，也不用担心，大不了把用户下所有的索引都重组一遍）

大表重组的其他方式

可以采取导入导出方式，比如先将用户导出，再将用户DROP掉，最后重建用户，重新导入

create or replace directory GoogleGoogle_BACKUP as 'D:\GoogleGoogle_BACKUP';
drop directory GoogleGoogle_BACKUP;
grant read,write on directory GoogleGoogle_BACKUP to test1;

--http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
select * from dba_directories t;
EXPDP system/pass1@ORCL CONTENT=ALL DIRECTORY= DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:ORCL_FROM_USER1_20130312_104634.DMPDP SCHEMAS=FROM_USER1 parallel=2 ;
IMPDP system/pass1@ORCL directory=DATA_PUMP_DIR DUMPFILE=DATA_PUMP_DIR:FROM_USER1.DMPDP REMAP_SCHEMA=FROM_USER1:TO_USER2 
                        REMAP_TABLESPACE=FROM_USER1_tablespace:TO_USER2_tablespace ignore=y table_exists_action=append status=30 feedback=5000;

还可以采取SHRINK

SELECT 'alter table ' ||t.owner || '.' || t.table_name || ' enable row movement;
' || 'alter table ' ||t.owner || '.' || t.table_name || ' SHRINK SPACE CASCADE;
' || 'alter table ' ||t.owner || '.' || t.table_name || ' disable  row movement;' 

FROM dba_tables t 
WHERE t.owner LIKE 'GoogleGoogle%'
;