Oracle 大索引重组、大表重组

来源：互联网发布：关于网络爱情的歌编辑：程序博客网时间：2024/06/11 03:56

大索引重组

复制查询结果，并执行

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.htmselect * 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%';

0 0