Oracle删除大表并回收空间的过程

最新推荐文章于 2024-11-27 13:03:50 发布
原创 最新推荐文章于 2024-11-27 13:03:50 发布 · 4.1k 阅读 · 2 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#oracle #delete #sql #table #date
#cache
本文介绍在Oracle数据库中批量删除大表数据的最佳实践，包括分批删除、逐次提交、回收表空间、处理索引失效等关键步骤，以及通过自制事务提高效率的方法。

如何删除大表中的数据

http://space.itpub.net/22664653/viewspace-704778
http://hi.baidu.com/ice_sky2008/blog/item/b4bd43fd4a45bb4cd6887da7.html
http://hemin108.iteye.com/blog/547282
http://hemin108.iteye.com/blog/547282
http://psoug.org/reference/truncate.html
http://www.njeit.cn/show.aspx?id=827&cid=65###
http://www.360doc.com/content/10/0317/13/925373_19114692.shtml

上一篇 / 下一篇  2011-08-12 22:33:11 / 个人分类：Oracle

查看( 77 ) / 评论( 0 ) / 评分( 0 / 0 )
通常我们认为truncate和drop都是ddl语句,都会释放表占用的空间,且不可回退;而他们的之间的区别在于我们平时忽略的reuse/drop storage子句.
reuse storage不会立即释放表的extent,我们可以先使用truncate table tableName reuse storage,然后分批释放表的extent.这在删除大表时非常有用,避免大量的io操作,影响整体性能.如果使用默认的drop storage就会立即释放extent,删除的表如果非常大,这对系统有时候这可能是灾难性的.
版本：10.2.0.1 
说明：在9i下如此操作为报错(后续说明)。
实验过程如下:
SQL> insert into yanga select * from yanga;
50474 rows created.
SQL> /
100948 rows created.
SQL> /
201896 rows created.
SQL> /
403792 rows created.
SQL> /
807584 rows created.
SQL> /
1615168 rows created.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
       349
SQL>  truncate table yanga reuse storage;
Table truncated.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
       349
SQL>alter table yanga deallocate unused keep 50M;
Table altered.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
   50.0625
SQL>alter table yanga deallocate unused keep 10M;
Table altered.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
   10.0625
SQL>alter table yanga deallocate unused keep 0 M;
Table altered.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
     .0625
SQL>drop table yanga purge;
Table dropped.
SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');
        MB
----------
以上是10g2的操作。




http://www.cnblogs.com/killkill/archive/2009/06/30/1514015.html

近日在查询某项日志的时候，发现查询非常缓慢，根据以往的经验这是由于某个日志表过大引起的，为了加快查询，决定将大部分的历史数据迁移到另外一个表中，本文主要记录删除这个大表的过程，就解决问题而言还有很多方法，但是本文侧重点在于如何处理大数据量删除的操作，及其善后工作。

首先看看我们要做数据迁移的表所占的空间：

SQL> select segment_name , bytes/1048576 MB
  2  from user_segments
  3  order by bytes
  4  /

SEGMENT_NAME                                               MB
-------------------------------------------------- ----------
TAB_NODE_PARAM                                           .375
SYS_LOB0000053047C00004$$                               .4375
PK_TAB_FLOW_HIS                                             1
PK_TAB_NODE_HIS                                             2
TAB_TASK_HIS                                                2
TAB_FLOW_HIS                                                4
TAB_NODE_HIS                                               17
PK_TAB_APPEXCHANGE_LOG                                    152
TAB_APPEXCHANGE_LOG                                       768

看看历史数据大概所占的空间比例：

SQL> select count(*) from tab_appexchange_log
  2  where receive_time >= to_date('2009-01-01','yyyy-mm-dd')
  3  union all
  4  select count(*) from tab_appexchange_log
  5  where receive_time < to_date('2009-01-01','yyyy-mm-dd')
SQL> /

  COUNT(*)
----------
   1584298
   4037710

从结果来看，的确存在很多历史数据，我们将 2009 年之前的数据迁移到别的表中。

SQL> create table tab_appexchange_log_20090101
  2  as
  3  select * from tab_appexchange_log
  4  where receive_time < to_date('2009-01-01','yyyy-mm-dd')
SQL> /

Table created.

SQL> select count(*) from tab_appexchange_log_20090101
  2  /

  COUNT(*)
----------
   4037710

数据已经迁移一份到新的表里面了，可以删除原表中的旧数据了。

SQL> delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')
  2  /
delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')
       *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

这里出现了一个 ORA-30036的错误，大概意思是undo表空间不足，看来是要删除的数据太多了，于是考虑化整为零，删一点，commit一下。

 

SQL> delete tab_appexchange_log where receive_time < to_date('2007-01-01','yyyy-mm-dd')
  2  /

0 rows deleted.

SQL> delete tab_appexchange_log where receive_time < to_date('2008-01-01','yyyy-mm-dd')
  2  /

1586227 rows deleted.

SQL> commit;

Commit complete.

SQL> delete tab_appexchange_log where receive_time < to_date('2008-06-01','yyyy-mm-dd')
  2  /

813019 rows deleted.

SQL> commit;

Commit complete.

SQL> delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')
  2  /

1638464 rows deleted.

SQL> commit;

Commit complete.

可以预知，现在表里面的数据应该只有 15***** 条，以下证实了这个想法。

SQL> select count(*) from tab_appexchange_log
  2  /

  COUNT(*)
----------
   1584522

我们再看看表占用空间的情况：

SQL> select segment_name , bytes/1048576 MB
  2  from user_segments
  3  order by bytes asc;

SEGMENT_NAME                                               MB
-------------------------------------------------- ----------
TAB_NODE_PARAM                                           .375
SYS_LOB0000053047C00004$$                               .4375
PK_TAB_FLOW_HIS                                             1
PK_TAB_NODE_HIS                                             2
TAB_TASK_HIS                                                2
TAB_FLOW_HIS                                                4
TAB_NODE_HIS                                               17
PK_TAB_APPEXCHANGE_LOG                                    152
TAB_APPEXCHANGE_LOG_20090101                              552
TAB_APPEXCHANGE_LOG                                       768

看来原表所占的空间还没有释放，这个涉及一个HWM的说法，还有可以观察到在TAB_APPEXCHANGE_LOG上面的一个索引 PK_TAB_APPEXCHANGE_LOG 空间没有回收。

我们回过头来看看刚才的操作对undo表空间产生的压力：

TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y   UNDOBLKS
------------------- ------------------- ----------
2009-06-30 13:39:32 2009-06-30 13:46:35      96164
2009-06-30 13:29:32 2009-06-30 13:39:32      89316
2009-06-30 13:19:32 2009-06-30 13:29:32     209885
2009-06-30 13:09:32 2009-06-30 13:19:32        105
2009-06-30 12:59:32 2009-06-30 13:09:32        125
2009-06-30 12:49:32 2009-06-30 12:59:32         74
2009-06-30 12:39:32 2009-06-30 12:49:32         80
2009-06-30 12:29:32 2009-06-30 12:39:32         72
2009-06-30 12:19:32 2009-06-30 12:29:32         76
2009-06-30 12:09:32 2009-06-30 12:19:32         89
2009-06-30 11:59:32 2009-06-30 12:09:32        126

呵呵，刚才DML的操作对undo表空间产生的压力还是非常可观的。

我们开始回收表所占的空间：

SQL> alter table TAB_APPEXCHANGE_LOG enable row movement;

Table altered.

SQL> alter table TAB_APPEXCHANGE_LOG shrink space;

Table altered.

 

由于这么大规模的删除，会导致很多索引中存在很多标记为删除的节点，占地方还不能发挥作用，因此重建索引将这些无用节点清理出去才是上策。

SQL> analyze index PK_TAB_APPEXCHANGE_LOG  validate structure;

Index analyzed.

SQL> select height, name , LF_ROWS_LEN,LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN from index_stats;

    HEIGHT NAME                           LF_ROWS_LEN    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ------------------------------ ----------- ---------- ----------- ---------------
         3 PK_TAB_APPEXCHANGE_LOG            88905533    5621608     4036429        63558680

SQL> alter index PK_TAB_APPEXCHANGE_LOG rebuild online;

Index altered.

SQL> analyze index PK_TAB_APPEXCHANGE_LOG  validate structure;

Index analyzed.

SQL> select height, name , LF_ROWS_LEN,LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN from index_stats;

    HEIGHT NAME                           LF_ROWS_LEN    LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ------------------------------ ----------- ---------- ----------- ---------------
         3 PK_TAB_APPEXCHANGE_LOG            25347109    1585195           0               0

如果现在我们再查一下索引所占的空间的话，会发现所占空间是没有减少的，和表一样，同样也是需要收缩一下空间。

SQL> alter index PK_TAB_APPEXCHANGE_LOG shrink space;

Index altered.

SQL> select segment_name , bytes/1048576 MB
  2  from user_segments
  3  order by bytes
  4  /

SEGMENT_NAME                                               MB
-------------------------------------------------- ----------
TAB_NODE_PARAM                                           .375
SYS_LOB0000053047C00004$$                               .4375
PK_TAB_FLOW_HIS                                             1
TAB_TASK_HIS                                                2
PK_TAB_NODE_HIS                                             2
TAB_FLOW_HIS                                                4
TAB_NODE_HIS                                               17
PK_TAB_APPEXCHANGE_LOG                                28.1875
TAB_APPEXCHANGE_LOG                                  220.8125
TAB_APPEXCHANGE_LOG_20090101                              552

 

自此一次删除大表的操作完成，回顾一下，主要有以下几点：

1。分批删除数据，按批次提交。

2。回收表所占的空间。

3。处理由于表收缩所导致的索引失效问题。


http://psoug.org/reference/truncate.html

drop table与db cache


我们都知道drop table, truncate table时都会先做一次checkpoint，将被删除对象的脏块写入磁盘。

客户有一套系统，Oracle 9.2.0.8，需要做数据迁移，由于种种原因，采用的是逻辑迁移的方式。由于库比较大，超过了1.5T，而停机时间又有限，因此在正式迁移之前需要做大量的测试，测试的目的，一方面是看迁移流程上是否存在问题，另一方面是看迁移的时候，哪个地方会存在性能瓶颈，并进行优化，同时估算实施迁移时间。

第一次测试后，需要把测试产生的大量用户及其对象全部删除，删除用的是drop user username cascade。不幸的是这种方式删除得相当地慢。一个9000多个表的用户，删除了1个半小时才删除了4000多个表。为什么这么慢？有没有办法提高速度？

drop table既然要做checkpoint，那么在db cache非常大的情况下，这需要消耗的时间是比较长的。如果能够减少这个时间无疑将大幅提高速度。首先尝试做一次checkpoint，将buffer cache全部刷新出去：

view plain copy to clipboard print ?
  1. alter system checkpoint;   
  2. alter session set events 'immediate trace name flush_cache level 1';  
alter system checkpoint;
alter session set events 'immediate trace name flush_cache level 1';

发现没什么效果。

由于db_cache_size有50GB左右，db_keep_cache_size有6G左右。重新设置参数，将db_keep_cache_size设为0，将db_cache_size设为200M，重启一下数据库，重新执行删除用户的操作，操作很快完成。

在另一次同样的过程中，采用同样的修改参数的方式，效果同样非常明显。

这是个简单的案例，与君共享。


http://www.laoxiong.net/drop-table-and-db-cache.html

http://caihorse.iteye.com/blog/614570

http://psoug.org/reference/truncate.html

« Control SCN of Undo Segments | Blog首页 | Jonathan Lewis: Can we have a sensible debate ? »

Oracle中大批量删除数据的方法

作者： eygle | English Version 【转载时请以超链接形式标明文章 和作者信息及 本声明】
链接：
站内相关文章|Related Articles
Oracle中审计删除（DELETE）操作的触发器
dbms_sqltune解析SQL的BIND_DATA绑定变量值
SQL 共享之 ROLL_INVALID_MISMATCH 含义
CBO中 SMON 进程与 col_usage$ 的维护
10g临时表空间组导致递归SQL高度解析案例
批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。

首先创建一下过程，使用自制事务进行处理： 
create or replace procedure delBigTab
(
p_TableName       in    varchar2,
p_Condition       in    varchar2,
p_Count        in    varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
以下是删除过程及时间: 
SQL> create or replace procedure delBigTab
  2  (
  3    p_TableName       in    varchar2,
  4    p_Condition       in    varchar2,
  5    p_Count        in    varchar2
  6  )
  7  as
  8   pragma autonomous_transaction;
  9   n_delete number:=0;
 10  begin
 11   while 1=1 loop
 12     EXECUTE IMMEDIATE
 13       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
 14     USING p_Count;
 15     if SQL%NOTFOUND then
 16        exit;
 17     else
 18              n_delete:=n_delete + SQL%ROWCOUNT;
 19     end if;
 20     commit;
 21   end loop;
 22   commit;
 23   DBMS_OUTPUT.PUT_LINE('Finished!');
 24   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
 25  end;
 26  /

Procedure created.

SQL> set timing on
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)
------------------
          11000000

Elapsed: 00:00:00.23
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.54
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)
------------------
          11100000

Elapsed: 00:00:00.18
SQL> set serveroutput on
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');
Finished!
Totally 96936 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61
10万记录大约19s

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.85
SQL> 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');
Finished!
Totally 1000000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:03:13.87

100万记录大约3分钟
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');
 
Finished!
Totally 6999977 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:27:24.69
700万大约27分钟

以上过程仅供参考.

http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html



oracle回收表空间命令,Oracle 回收表空间文件空闲的空间
weixin_39520013的博客
04-06 2122
我们知道Oracle对于使用DELETE删除的数据，表空间的剩余空间并不会增加。虽然数据已经删除了，但是已使用的空间还是那么大。这就造成了一个现象，当一个表空间使用率达到100%的时候，我们通过删除一些没用的数据，但是还是不能往放在该表空间下的表添加数据。(本文地址：http://blog.csdn.net/sunansheng/article/details/51453939)Oracle EB...
Oracle使用delete删除部分表数据后，如何释放表空间？？
weixin_63610637的博客
05-25 7240
Oracle使用delete删除部分表数据后，如何释放表空间？？
[DB] Oracle怎么收缩表空间.
专注于数据
11-17 649
表空间的数据删除以后,它所占据的表空间并没有释放,所以这时候还需要去收缩以下这张表的表空间     SQL语句为: alter table DM_ESSTAB enable row movement;alter table DM_ESSTAB shrink space cascade;
Oracle 10g及以上版本表空间回收
星叶知佳人的博客
03-27 521
大量使用delete，久而久之会造成表空间越来越大，不管怎么delete删除数据表空间还是没有变化。这里讲表的碎片整理，而不是通过重建表来整理出空闲表空间，毕竟上亿的数据要重建，似乎难度太大。 项目上都会记录日志，对于诊断 or 测试类的项目, 记录的日志比较多, 所需要的表空间也是比较大。对于含有大数据字段（clob）的日志所需要的表空间就更加多了。 我在项...
Oracle回收表空间
wyanyi的博客
07-22 1646
【代码】Oracle回收表空间。
Oracle数据库表空间整理回收与释放操作
Samson_light的博客
05-12 1万+
前言 在使用Oracle进行录入测试时，我们会制造许多测试数据，而使用删除后表空间的高水位依旧无法自动下调，导致出现表空间出现空白数据，影响表空间性能的同时也占用了过多的存储空间。
oracle回收表空间
Th record of huo.
04-08 776
2. 查看表分配空间大小。1. 手工计算实际大小。
Oracle 删除表空间回收磁盘空间
小单的博客专栏
01-30 4628
表空间被删除后，表空间的数据文件并不会被系统回收并释放，所以还需要手工删除它。3、再次查询表空间的数据库文件对应表空间名称是否还存在。1、查询表空间和表空间数据库文件路径。4、手工删除表空间物理文件。
oracle数据库delete表数据后进行物理表空间回收
YByuanrensheng的博客
09-18 2243
3.RESIZE表空间文件大小，根据步骤二确定resize大小，比如实际使用3000M，分配表空间文件20000M，此时resize可为5000M。substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率。where a.file_id=b.file_id and b.tablespace_name=‘表空间名’(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,b.file_id 物理文件号,
Oracle回收站空间使用与清理
m0_37625564的博客
02-22 6029
DROP表后还可以从回收站中查到表，但是在DBA_FREESPACE中已经把DROP的表的空间回收为可用了。 要是此时在表空间建表并空间不断增长，回收站中DROP表 最终会被覆盖或者叫被删除来释放空间，此时闪回DROP的表会报错 ：ORA-38305: object not in RECYCLE BIN 如果创建的表空间是自动扩展的，则 会首先先将回收站中表彻底删除，如表空间内的可用空间还不够，才会自动扩展。 SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,TS
Oracle 数据库中的空间回收和表收缩
南朝雨
04-27 6008
最近遇到了oracle数据库中的一个问题，数据库文件异常偏大，但实际业务中数据量并不大，经过查询具体的表数据空间占用情况，发现即使是一些空表，占用空间也很大。查了下资料，实际是数据表由于之前存在过很多数据，造成表的的水位线 (HWM)很高，即使数据删除后也无法后退来减小表大小和回收空间。采用以下方式得到解决。 表空间使用情况查询 可使用下面语句查询表空间的大小及使用情况： SELECT TABLE...
Oracle SYSTEM 和 SYSAUX 表空间的清理和回收
最新发布
驰兔的博客
11-27 2247
本文将详细介绍如何清理和回收 SYSTEM`和 SYSAUX 表空间的空间
Oracle表空间满清理方案汇总分享
美国梦中国心
04-09 1万+
Oracle中表空间的扩容汇集。喜欢点个赞加关注。
Oracle 回收表空间文件空闲的空间
热门推荐
苏南生的CSDN博客
05-19 2万+
我们知道Oracle对于使用DELETE删除的数据，表空间的剩余空间并不会增加。虽然数据已经删除了，但是已使用的空间还是那么大。这就造成了一个现象，当一个表空间使用率达到100%的时候，我们通过删除一些没用的数据，但是还是不能往放在该表空间下的表添加数据。Oracle EBS有张表fnd_lobs，该表包含字段FILE_DATA，用于存放Forms的“文件-导出”命令导出的文件。每导出一次数据，就
回收某个表使用空间的步骤
sunna0519的专栏
03-22 523
    虽然每天都有删除一些数据，但不释放表空间。所以每次只能增大表空间大小来降低占用率，可硬盘空间已经快用完了。该怎么办呢？方法一： 1、选择某个表空间中超过N个blocks的segments，通过此语句可以看出那个表占用的空间大。     select segment_name,segment_type,blocks from dba_segments
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

福海鑫森

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值