大索引重组
复制查询结果,并执行
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;
注意要先执行以上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%'
;