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