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

--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                      1719906            0  117470482    --shrink后统计信息更新了