The UNDO tablespace over-expansion, how can you do.

undo tablespace to keep the the data modification mirror, corresponding undo segment dedicated to storage, Oracle is also used to maintain the consistency of read (consistent read) with the rollback of the transaction to achieve and maintain transaction data read take. And transaction recovery (recovery transaction). Undo tablespace in 10g also has a special function is to do table-level Flashback (Flashback). The undo tablespace do not know is not automatically shrink a document to say that this will have an Oracle database management system, but in reality the undo tablespace blindly expansion sometimes take up a lot of storage space, our system sometimes cause a crash the damage, as an administrator, we have to factor that endangers the health of the database are eliminated. So how do we demonstrate over-occupied undo tablespace.

With us look.

1 Make sure the table space name
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
————————–
undo_tablespace string UNDOTBS1

Find tablespace datafile
SQL> select * from dba_data_files where tablespace_name = ‘UNDOTBS1’;
File_name TABLESPACE_NAME the bytes
—————————————–
/ Opt/oracle/oradata/undotbs01.dbf UNDOTBS1 209715200

SQL> select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
—————————
00 0.00035858154296875 0.00035858154296875 0
2 0 0.00109100341796875 .00304412841796875 two
40 0.00109100341796875 0.00304412841796875 2
10 0 0.00109100341796875 .00206756591796875 a
50 0.00109100341796875 0.00206756591796875 1
70 0.00109100341796875 0.00206756591796875 1
10 0.00206756591796875 0.00206756591796875 1
30 0.00206756591796875 0.00304412841796875 1
80 0.00206756591796875 0.00304412841796875 1
90 0.00206756591796875 0.00304412841796875 1
60 0.00218963623046875 0.00218963623046875 1

Create a new undo tablespace
SQL> create undo tablespace undotbs2 datafile ‘/ opt/oracle/oradata/undotbs02.dbf’ size 50M autoextend on next 50M maxsize 200M extent management local;

Switch Udon table space to new table space
SQL> alter system set undo_tablespace = undotbs2 scope = both;

Query after switching table space
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
————————–
undo_tablespace string UNDOTBS2

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

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
—————————
110 0.00011444091796875 0.00011444091796875 0
120 0.00011444091796875 0.00011444091796875 0
130 0.00011444091796875 0.00011444091796875 0
150 0.00011444091796875 0.00011444091796875 0
170 0.00011444091796875 0.00011444091796875 0
190 0.00011444091796875 0.00011444091796875 0
200 0.00011444091796875 0.00011444091796875 0
180 0.00011444091796875 0.00011444091796875 0
160 0.00011444091796875 0.00011444091796875 0
140 0.00011444091796875 0.00011444091796875 0
00 0.00035858154296875 0.00035858154296875 0

Drop off the original table space
SQL> drop tablespace undotbs1 including contents and datafiles;