ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】删除大表操作一则

【Oracle】删除大表操作一则

原创 Linux操作系统 作者:杨奇龙 时间:2012-04-06 17:37:31 0 删除 编辑
      因为数据库空间不足,需要对历史数据进行清理,查询涉及的表竟然有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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-720583/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7498327