清理Oracle大表

1、查看表的大小

SQL>select owner,
       segment_name，
       segment_type,
       tablespace_name,
       round(bytes / 1024 / 1024 / 1024, 0) GB
  from dba_segments
 where segment_name='TEST';

OWNER   SEGMENT_NAME  SEGMENT_TYPE   TABLESPACE_NAME     GB
------- ------------  -------         ----------------- ----
SCOTT    TEST          TABLE             USERS           10

2、获取表的定义

SQL>select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual;

3、查看表的依赖关系

SQL>select * from user_dependencies t where t.referenced_name = 'TEST';

4、查看对象的状态：

SQL>select owner, object_name, object_type, status from dba_objects where object_name in ('TEST_PKG','TEST1_PKG');

5、将表重命名

SQL>alter table TEST rename to TEST_B;

6、根据抽取的表的定义，重建新表

7、查看失效的对象

SQL>select owner, object_name, object_type, status from dba_objects where object_name in ('TEST_PKG','TEST1_PKG')

8、重新编译对象：

SQL>select 'ALTER ' ||
       decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' ||
       owner || '.' || OBJECT_NAME ||
       decode(object_type,
              'PACKAGE BODY',
              ' COMPILE BODY ; ',
              'PACKAGE',
              ' COMPILE SPECIFICATION ; ',
              ' COMPILE; ') aa
  from dba_objects
 where status <> 'VALID'
   and dba_objects.owner in ('SCOTT')
   AND object_name in ('TEST_PKG','TEST1_PKG');

生成如下的编译脚本：执行编译脚本

ALTER PACKAGE SCOTT.TEST_PKG COMPILE BODY ; 
ALTER PACKAGE SCOTT.TEST1_PKG COMPILE BODY ; 

9、清理旧表：TEST_B

SQL>truncate table TEST_B reuse storage;

分批释放大小：

SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 8G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 6G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 4G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 2G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 0G;

查看释放后的表的大小：

SQL>select owner,
       segment_name，
       segment_type,
       tablespace_name,
       round(bytes / 1024 / 1024 / 1024, 0) GB
  from dba_segments
 where segment_name='TEST_B';

10、删除旧表

SQL>drop table TEST_B purge;