【Oracle】删除大表操作一则

      因为数据库空间不足，需要对历史数据进行清理，查询涉及的表竟然有550G，和开发沟通之后将历史数据使用应用程序迁移到其他机器上，之后对旧表进行删除！(对于此种情况多少有些无奈，入职之前表已经存在了，建表的时候应该考虑使用分区表，清理数据会更方便)

 查看表的大小

YANG@yangdb>set timing on;

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            550.075195

Elapsed: 00:00:00.98

YANG@yangdb>

使用 truncate的reuse storage 特性，默认时是drop storage，这样会直接对object占用的删除之后并不直接drop storage ，这样可以避免回收大量的extent 太多导致系统资源紧张的情况

YANG@yangdb>truncate table YANG.YANGTAB reuse storage;

Table truncated.

Elapsed: 00:04:19.31

YANG@yangdb>

YANG@yangdb>

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            550.075195

Elapsed: 00:00:00.09

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 563277M;

ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 563277M

*

ERROR at line 1:

ORA-03230: segment only contains 72099438 blocks of unused space above high water mark

Elapsed: 00:00:00.27

第一次 size 设置的有点大！

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 503277M;

Table altered.

Elapsed: 00:00:17.99

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            491.481628

Elapsed: 00:00:00.03

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 453277M;

Table altered.

Elapsed: 00:00:14.50

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            442.653503

Elapsed: 00:00:00.03

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 403277M;

Table altered.

Elapsed: 00:00:14.86

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            393.825378

Elapsed: 00:00:00.03

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 323277M;

Table altered.

Elapsed: 00:00:22.53

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            315.700378

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 253277M;

Table altered.

Elapsed: 00:00:28.05

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            247.341003

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 183277M;

Table altered.

Elapsed: 00:00:54.36

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            178.981628

Elapsed: 00:00:00.13

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 123277M;

Table altered.

Elapsed: 00:00:33.64

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            120.387878

Elapsed: 00:00:00.03

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 83277M;

Table altered.

Elapsed: 00:00:22.36

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            81.3253784

Elapsed: 00:00:00.03

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 53277M;

Table altered.

Elapsed: 00:00:14.35

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            52.0285034

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 33277M;

Table altered.

Elapsed: 00:00:09.40

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            32.4972534

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 13277M;

Table altered.

Elapsed: 00:00:09.29

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            12.9660034

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 3277M;

Table altered.

Elapsed: 00:00:04.44

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            3.20037842

Elapsed: 00:00:00.02

YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 277M;

Table altered.

Elapsed: 00:00:03.08

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            .270690918

Elapsed: 00:00:00.07

YANG@yangdb>drop table YANG.YANGTAB;

Table dropped.

Elapsed: 00:00:01.11

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB_NEW';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB_NEW                        83.7783203

Elapsed: 00:00:00.09

Elapsed: 00:00:00.01

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB_NEW';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB_NEW                        83.7783203

YANG@yangdb>rename YANGTAB_new to YANGTAB;

Table renamed.

Elapsed: 00:00:00.10

YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';

SEGMENT_NAME                   BYTES/1024/1024/1024

------------------------------ --------------------

YANGTAB                            83.7783203

Elapsed: 00:00:00.03

YANG@yangdb>

Elapsed: 00:00:00.03

附上操作过程中遇到的低级错误

1 oracle 和mysql 之间对表的重命名的语法混淆了，汗！

YANG@yangdb>rename table YANG.YANGTAB_new to YANG.YANGTAB;

rename table YANG.YANGTAB_new to YANG.YANGTAB

       *

ERROR at line 1:

ORA-00903: invalid table name

2 表名不允许带owner

YANG@yangdb>rename YANG.YANGTAB_new to YANG.YANGTAB;

rename YANG.YANGTAB_new to YANG.YANGTAB

       *

ERROR at line 1:

ORA-01765: specifying owner s name of the table is not allowed

Elapsed: 00:00:00.01

参考自己的另一篇文章

http://space.itpub.net/22664653/viewspace-704778