前一天晚上做大表删除操作测试，分段删除，没删除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]$ dbaSQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 09:23:18 2013Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.ERROR:ORA-09817: Write to audit file failed.Linux-x86_64 Error: 28: No space left on deviceORA-01075: you are currently logged onEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

检查磁盘空间，爆满

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

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

[oracle@centos5 osa]$ sqlplus test/testSQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 09:23:26 2013Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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  176544060SQL> 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  176544060SQL>

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 - oraclecd [oracle@centos5 ~]$ sqlplus test/testSQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 21 11:39:40 2013Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @space.sqlTABLESPACE_NAME      TOTAL       USED RATIO            FREE  MAX_BYTES--------------- ---------- ---------- ---------- ---------- ----------SYSTEM                 490     486.69   99.32          3.31       2.94SYSAUX                 380        377   99.21             3        .81TEST                 40960   20072.19   49.00      20887.81       3968TS_OA                   50       5.06   10.12         44.94      39.94USERS                    5        .44    8.80          4.56       4.56TS_URP                 200       5.37    2.69        194.63     190.81UNDOTBS1              9195     134.31    1.46       9060.69       2965OSA_TEST               500       7.31    1.46        492.69     485.94TS_IMPTEST              50        .06    0.12         49.94      49.949 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、建立一个全新的undostb2SQL>  create undo tablespace undotbs2 datafile '/home/oracle/oradata/osa/undotbs2.dbf' size 200M;Tablespace created.--2、修改系统的undo_tablespace为undotbs2SQL> alter system set undo_tablespace=undotbs2 scope=both;System altered.--3、删除原有undo tablespaceSQL> 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        .63SYSTEM                 490     486.69   99.32          3.31       2.94TEST                 40960   20072.19   49.00      20887.81       3968TS_OA                   50       5.06   10.12         44.94      39.94USERS                    5        .44    8.80          4.56       4.56TS_URP                 200       5.37    2.69        194.63     190.81OSA_TEST               500       7.31    1.46        492.69     485.94UNDOTBS2               200       1.31    0.66        198.69     198.69TS_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  176544060SQL>  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                      1719906            0  117470482    --shrink后统计信息更新了

如果是生产系统那会很惨，日常监控要非常注意数据库空间、系统空间