当前位置：首页 -> AI编程基础 -> 数据库编程

delete大批量数据引起空间爆满处理(一)

2014-11-24 14:05:37 · 作者: · 浏览: 0
标签: delete 大批量 数据 引起 空间 爆满 处理
delete大批量数据引起空间爆满处理
晚上做大表删除操作测试，分段删除，没删除100000条commit一次，由于存储过程打印了执行时间中途断网，无法看到执行时间
于是直接kill了任务
结果早上登录测试 数据库发现空间爆满
然后一时糊涂去弄了表的shrink跟dbms_stats.gather_table_stats想着收缩表释放空间
弄到一半突然想起，表的收缩最多是释放表空间的空闲空间，并非系统空间
后来想想，大量删除反复操作过程引发了undo的扩展，后来检查表空间后确实如此
最后在线更换undo tablespace后删除原tablespace后恢复
附上存储过程 
 create or replace procedure delete_table as
 i number(15);
 l_start number default dbms_utility.get_time;
 begin
    dbms_output.put_line('begin time:'||to_char(SYSTIMESTAMP,'HH24:MI:SS:FF2'));
    i:=0;
    for x in (select log_id from user_log where user_log_id<46380000)  
    loop
    delete from user_log where user_log_id = x.user_log_id;
    i:=i+1;
    if (i>10000) then
        begin
            --dbms_output.put_line('delete ok.'||i);
            commit;   
            i:=0;  
        end;    
    end if;  
    end loop;
    commit;
    dbms_output.put_line('ok.end time:'||to_char(SYSTIMESTAMP,'HH24:MI:SS:FF2'));
    exception when others then
    rollback;
end;
 /

另外调试时记得 
 set serveroutput on

遇到的报错提示 
[oracle@centos5 osa]$ dba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 09:23:18 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
ORA-01075: you are currently logged on


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

检查磁盘空间，爆满


[oracle@centos5 osa]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              93G   89G     0 100% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                1006M     0 1006M   0% /dev/shm

想着收缩下大表就有空间了,,,糊里糊涂开始shrink



[oracle@centos5 osa]$ sqlplus test/test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 09:23:26 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS  from user_tables where table_name = upper('user_log');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
user_log                      2606643            0  176544060

SQL> alter table user_log enable row movement;

Table altered.

SQL> alter table user_log shrink space cascade;  --索引也能缩小

Table altered.

SQL> select table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS  from user_tables where table_name = upper('user_log');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
user_log                      2606643            0  176544060

SQL>

shrink后还需要重新收集统计信息，但是当空间爆满时同样无法进行统计信息收集


SQL> exec dbms_stats.gather_table_stats('TEST','user_log');
BEGIN dbms_stats.gather_table_stats('TEST','user_log'); END;

*
ERROR at line 1:
ORA-01114: IO error writing block to file %s (block # %s)

想想其实收缩、整理，只是释放表空间的已用空间到空闲空间，并不会回收实际的磁盘
先删除了一些本身在在根目录下的oracle安装文件才可以做undo表空间的重建工作，否则没空间还是无法工作
由于大量操作导致了undo的自动扩展，占用了大量空间，准备收缩undo 
[root@centos5 ~]# su - oracle
cd [oracle@centos5 ~]$ sqlplus test/test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 11:39:40 2013

Copyright (c) 1982, 200