重建undotbs释放过度占用的空间

重建undotbs释放过度占用的空间

 

数据库的undotbs占用空间太大，磁盘空间有点紧，先把undotbs重建一下。

创建新的UNDO表空间
SQL> CREATE SMALLFILE UNDO
   2       TABLESPACE "UNDOTBS2"
   3       DATAFILE '/sysion/oracle/oradata/UNDOTBS2A.dbf' SIZE 1000M
   4       AUTOEXTEND
   5       ON NEXT   10M MAXSIZE UNLIMITED, '/sysion/oracle/oradata/
   6       UNDOTBS2B.dbf' SIZE 1000M AUTOEXTEND
   7       ON NEXT   10M MAXSIZE UNLIMITED
   8   /
附：这个地方由于目录名于文件名不在同一行，导致实际的文件UNDOTBS2B.dbf名前面多了个空格，下次一定要注意！！！
Tablespace created

SQL>
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
   2   from v$rollstat order by USN;

        USN       XACTS STATUS           RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024     SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
          0           0 ONLINE             0.00035858154296875     0.00035858154296875           0
          1           0 ONLINE              0.0284347534179688       0.174919128417969        1092
          2           0 ONLINE              0.0196456909179688       0.161247253417969        1343
          3           0 ONLINE              0.0206222534179688       0.546989440917969        1536
          4           0 ONLINE              0.0440597534179688       0.505973815917969        1922
          5           0 ONLINE             0.00109100341796875      0.0792160034179688         150
          6           0 ONLINE              0.0215988159179688       0.260917663574219        1506
          7           0 ONLINE              0.0372238159179688       0.287223815917969        1655
          8           0 ONLINE             0.00402069091796875       0.258903503417969        1369
          9           0 ONLINE              0.0225753784179688       0.259941101074219        1473
         10           0 ONLINE              0.0342941284179688       0.258903503417969        1690
         11           0 ONLINE             0.00890350341796875       0.263786315917969        1549

12 rows selected

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered

检查UNDO Segment状态
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
   2   from v$rollstat order by USN;

        USN       XACTS STATUS           RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024     SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
          0           0 ONLINE             0.00035858154296875     0.00035858154296875           0
         65           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         66           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         67           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         68           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         69           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         70           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         71           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         72           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         73           0 ONLINE             0.00011444091796875     0.00011444091796875           0
         74           0 ONLINE             0.00011444091796875     0.00011444091796875           0

11 rows selected

删除原UNDO表空间及数据文件
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped