删除数据后如何及时释放存储空间

删除数据后如何及时释放存储空间？--黄海明

当表空间大小受限时，即便通过delete带条件删除部分数据，被删除数据所使用的空间，依然不会被释放。此时想要再添加新的数据可能会得到“磁盘空间不足”的报错。

本文通过一个实验场景，介绍清理回滚段及时释放存储空间的方法，用以解决上述困扰。一．创建实验场景步骤如下（注：本实验场景中数据页的大小16k）：

1.删除测试环境中的表和表空间（确保数据库中TEST表空间及TEST_CLOB表仅为本

实验所用）

SQL> drop table test_clob;

SQL> drop tablespace test;

2.创建test表空间，大小为64M，并且务必关闭表空间自动扩展功能

SQL> create tablespace test datafile '/dm7/data/DAMENG1/test.dbf' size

64 autoextend off;

3.创建表

create table test_clob

(

c1 int,

c2 clob )

storage(on test, clusterbtr);

4.构建一个往test_clob表中增加大量数据匿名块

declare

begin

for i in 1..10000 loop

insert into test_clob values(1, lpad('测试',32000,'空间限制'));

commit;

end loop;

end;

/

等待匿名块执行完成，会收到如下报错：

[-523]:磁盘空间不足.

5.查看插入了多少条记录

SQL> select count(1) from test_clob;

行号 COUNT(1)

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

1 1493

已用时间: 27.543(毫秒). 执行号:2088.

6.删除500条记录

SQL> delete from test_clob where rowid <= 500;

SQL> commit;

查询记录条数

SQL> select count(1) from test_clob;

行号 COUNT(1)

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

1 993

已用时间: 1.809(毫秒). 执行号:2091.

再插入数据，看看是否报错？

SQL> insert into test_clob values(1, lpad('测试',32000,'空间限制'));

insert into test_clob values(1, lpad('测试',32000,'空间限制'));

[-523]:磁盘空间不足.

已用时间: 3.407(毫秒). 执行号:2092.

truncate好用，然而不能带条件删除，表空间大小本身又有局限，怎么才能将尚未释放的存储空间用起来？

二．解决方法：

7.以DBA身份创建存储过程

create or replace procedure remove_space()

as

begin

execute immediate 'alter system set ''UNDO_RETENTION''=1;';

dbms_lock.sleep(10);

execute immediate 'alter system set ''UNDO_RETENTION''=900;';

end;

/

以DBA身份调用上述存储过程

call remove_space();

8.再插入500条试试，看是否报错？

declare

begin

for i in 1..500 loop

insert into test_clob values(1, lpad('测试',32000,'空间限制'));

commit;

end loop;

end;

/

查一下总记录数

SQL> select count(1) from test_clob;

行号 COUNT(1)

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

1 1493

已用时间: 2.333(毫秒). 执行号:2096.

问题解决。

三．原理描述：

执行完第6步，添加数据时，依然收到“磁盘空间不足”的报错，因为这时候应用回滚段还未清理，磁盘空间并未释放。

未清理原因：由于需要根据回滚记录回溯、还原物理记录的历史版本信息，而不能在事务提交时立即清除当前事务产生的回滚记录。但是，如果不及时清理回滚段，可能造成回滚段空间的不断膨胀，占用大量磁盘空间。

DM基于上述原因提供了自动清理、回收回滚段空间的机制。采取保留回滚段一段时间，然后自动清理回滚段空间的方式。这个保留回滚段的时间长度由

UNDO_RETENTION参数指定，默认数值是900，单位是秒。

这个参数是系统级动态参数，修改后即时生效，dm.ini 文件里可以查到它的值。

通过第7步，调整UNDO_RETENTION为1秒，并预留10秒给进程清理回滚段，之后将UNDO_RETENTION恢复为900秒。

以上便是关于如何通过调整UNDO_RETENTION参数值，迅速解决delete数据后不能及时释放表空间的问题的方法了。