ORACLE数据库数据清理

内容概要

通过本文的知识，掌握数据常见的几种清理方法，并能熟练的使用本文提供的删除数据的存储过程进行数据清理。

1

数据量增长问题

业务数据库基本都会随着数据库的运行时间增加出现数据库占用空间增大的情况，相应的出现数据库运行变慢，特别是由于数据库规划阶段并未提供数据清理手段，一旦出现索引失效等问题，更加重了IO的负荷，最终出现严重的数据库性能故障。因此在数据库设计阶段提供较好的数据清理手段，将数据库数据维护在一定周期的窗口之中，可较好的避免故障，极大的提升数据库运行的稳定性。

常见的数据库清理有三种方式，delete，drop 分片，drop 表。大家都知道drop的效率要远远高于delete方式，但由于直接drop业务数据表显然不合适，在有时间分片的表上使用drop历史分片就是较好的选择了。

2

分片清理方法

这种清理方法对于数据来讲，要求能区分过期数据，如严格的带时间字段的性能数据，就可以通过时间来进行分片存储。

一般实施步骤为通过脚本自动根据配置对表进行分片和删除的操作，方法相对简单，就不在赘述，但由于是通过脚本程序来实现分片，一旦脚本运行失败，新的分片无法创建，自然就会导致数据无法插入的问题，最终变成业务故障。鉴于此问题可以在oracle 11g之后使用间隔分片。操作方法如下：

CREATE TABLE AAA

( FIRST_RESULT DATE,

SUM_LEVEL NUMBER(*,0),

DATA1 NUMBER(*,0)

)

PARTITION BY RANGE ("FIRST_RESULT") INTERVAL

(NUMTODSINTERVAL(1,'HOUR')) STORE IN ("DBS1", "DBS2", "DBS3", "DBS4")

(PARTITION"P_CLR1"VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

3

delete数据方法

对于无法使用分片进行数据清理的表，但又必须对过期数据进行删除的，由于delete涉及到数据一致性读的问题，因此delete会占用undo空间，单次过大的数据删除会导致锁表和undo空间过量使用等问题，小批量重复删除数据就成了首选，但手工1万条1万条的删除显然不是最佳选择。

下面提供一个删除数据的存储过程：

create or replace procedure delTable

(p_tablename in varchar2, --删除的表名

p_count in integer default 10000, --每次删除的数量

p_condition in varchar2 default '1=1' --加注的过滤删除条件

) as

num_del number := 0;

condition varchar2(500) := replace(p_condition,'"','''');

begin

while 1 = 1 loop

EXECUTE IMMEDIATE 'delete from '||p_tablename||' where '||condition||' and rownum

USING p_count;

if SQL%NOTFOUND then

exit;

else

num_del := num_del + SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE(num_del);

end if;

commit;

end loop;

commit;

DBMS_OUTPUT.PUT_LINE('delete successed，' || to_char(num_del) || ' rows deleted!');

end;

调用方法：

1、只带表名参数，全部删除

2、设置每次删除条数

3、设置删除数字字段条件

4、设置删除为字符串条件

说明

由于oracle字符串中使用单引号，为了简化参数输入，条件中所带的单引号，直接使用双引号。存储过程中进行处理。