重建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狀态

         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