如何优雅的删除Oracle数据库中的超大表

前言
由于磁盘空间不足，需要将数据库中的不用的大表删除来释放空间。
本文介绍了在避免大量的I/O操作，不影响数据库整体的性能的情况下，如何删除数据库中的一个513GB的大表。

下面是具体的操作步骤：

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           512
1
2
3
4
5
6
7
8
9
10
11

2、获取表的定义

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

3、查看表的依赖关系

如果表有依赖关系，需要识别依赖关系，如果强行删除，会导致业务不可用。这里要注意！

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

4、查看对象的状态

查看要删除的表和依赖的对象的状态

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

5、将表重命名

将表重命名的目的是根据表的定义，重建一个新表，让业务继续运行。
需要注意的是：表重命名后Oracle数据库自动把旧的对象上的完整性约束，索引，和权限迁移到新的对象上面。PACKAGE 不会失效。Oracle数据库上涉及与命名后的对象有关的例如 视图，同义词和存储过程和函数都会失效。PACKAGE BODY 会失效，需要重建。

SQL>alter table TEST rename to TEST_B;
1

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

7、查看失效的对象

表重命名后，数据库对象会失效，需要重新编译失效的数据库对象

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

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');
1
2
3
4
5
6
7
8
9
10
11
12
13

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

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

9、清理旧表

truncate和drop都是ddl语句，都会释放表占用的空间，且不可回退。
truncate和drop之间的区别在于reuse/drop storage的不同含义。
reuse storage不会立即释放表的extent，我们可以先使用truncate table tableName reuse storage语句truncate表，然后分批释放表的extent。这在删除大表时非常有用，避免大量的io操作，影响整体性能。
如果使用默认的drop storage就会立即释放extent，删除的表如果非常大，这对系统有时候这可能是灾难性的。

SQL>truncate table TEST_B reuse storage;
1

分批释放大小：

SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 400G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 300G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 200G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 100G;
SQL>ALTER table TEST_B DEALLOCATE UNUSED KEEP 0G;
1
2
3
4
5

查看释放后的表的大小：

SQL>select owner,
       segment_name，
       segment_type,
       tablespace_name,
       round(bytes / 1024 / 1024 / 1024, 0) GB
  from dba_segments
 where segment_name='TEST_B';
1
2
3
4
5
6
7

10、删除旧表

SQL>drop table TEST_B purge;
1