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, 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> @space.sql TABLESPACE_NAME TOTAL USED RATIO FREE MAX_BYTES --------------- ---------- ---------- ---------- ---------- ---------- SYSTEM 490 486.69 99.32 3.31 2.94 SYSAUX 380 377 99.21 3 .81 TEST 40960 20072.19 49.00 20887.81 3968 TS_OA 50 5.06 10.12 44.94 39.94 USERS 5 .44 8.80 4.56 4.56 TS_URP 200 5.37 2.69 194.63 190.81 UNDOTBS1 9195 134.31 1.46 9060.69 2965 OSA_TEST 500 7.31 1.46 492.69 485.94 TS_IMPTEST 50 .06 0.12 49.94 49.94 9 rows selected. SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1'; FILE_NAME BYTES/1024/1024 ----------------------------------- ---------------- /home/oracle/oradata/osa/undotbs01.dbf 9195
undo表空间的重建工作
--1、建立一个全新的undostb2
SQL> create undo tablespace undotbs2 datafile '/home/oracle/oradata/osa/undotbs2.dbf' size 200M;
Tablespace created.
--2、修改系统的undo_tablespace为undotbs2
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
--3、删除原有undo tablespace
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
--4、删除数据文件释放空间
rm -rf /home/oracle/oradata/osa/undotbs01.dbf
再来看看空间
SQL> @space.sql
TABLESPACE_NAME TOTAL USED RATIO FREE MAX_BYTES
--------------- ---------- ---------- ---------- ---------- ----------
SYSAUX 380 377.94 99.46 2.06 .63
SYSTEM 490 486.69 99.32 3.31 2.94
TEST 40960 20072.19 49.00 20887.81 3968
TS_OA 50 5.06 10.12 44.94 39.94
USERS 5 .44 8.80 4.56 4.56
TS_URP 200 5.37 2.69 194.63 190.81
OSA_TEST 500 7.31 1.46 492.69 485.94
UNDOTBS2 200 1.31 0.66 198.69 198.69
TS_IMPTEST 50 .06 0.12 49.94 49.94
再来收集统计信息查看下最高水位
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> exec dbms_stats.gather_table_stats('TEST','user_log');
PL/SQL procedure successfully completed.
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 
