How to deal with Oracle undo tablespace full

Steps to solve Oracle undo tablespace explosion:

1. Start SQLPLUS and log on to the database with sys.
# Su-oracle
$> Sqlplus/as sysdba

2. Find the UNDO tablespace name of the database and determine the UNDO tablespace used by the current routine:
Show parameter undo_tablespace.

3. Confirm the UNDO tablespace;
SQL> select name from v $ tablespace;
NAME
------------------------------
UNDOTBS1

4. Check the space occupied by the UNDO tablespace in the database and the storage location of the data files;
SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name like 'undotbs % ';

5. Check the usage of the rollback segment. Which user is using the resources of the rollback segment? If there is a user, it is best to change the time (especially in the production environment ).
SQL> select s. username, u. name from v $ transaction t, v $ rollstat r, v $ rollname u, v $ session s
Where s. taddr = t. addr and t. xidusn = r. usn and r. usn = u. usn order by s. username;

6. Check the UNDO Segment status;
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
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 44 1 0.00920867919921875 3.99295806884766 996
Three rollback objects exist in the restored tablespace.

7. Create a New UNDO tablespace and set automatic expansion parameters;
SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100 m reuse autoextend on next 50 m maxsize 5000 m;
Tablespace created.

8. Switch the UNDO tablespace to the new UNDO tablespace and dynamically change the spfile configuration file;
SQL> alter system set undo_tablespace = undotbs2 scope = both;
System altered.

9. Verify the UNDO tablespace of the current database [helper house http://www.bkjia.com]
SQL> show parameter undo
NAME TYPE VALUE
-------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS2
9. Wait for all undo segment offline of the original UNDO tablespace;
Select usn, xacts, status, rssize/1024/1024, hwmsize/1024/1024, shrinks from v $ rollstat order by rssize;
Select t. segment_name, t. tablespace_name, t. segment_id, t. status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
1 SYSTEM 0 ONLINE
2 _ SYSSMU1 $ UNDOTBS1 1 OFFLINE
3 _ SYSSMU2 $ UNDOTBS1 2 OFFLINE
4 _ SYSSMU47 $ UNDOTBS1 47 OFFLINE
The rollback segments corresponding to the UNDOTBS1 tablespace are OFFLINE.
 
10. Check whether the following content changes to $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora:
# Cat $ ORACLE_HOME/dbs/initddptest. ora
......
*. Undo_management = 'auto'
*. Undo_retention = 10800
*. Undo_tablespace = 'undotbs2'
......
If no change occurs, run the following statement:
SQL> create pfile from spfile;
File created.

11. Delete the original UNDO tablespace;
SQL> drop tablespace undotbs1 including contents;

Finally, you need to delete the data file in the path where the data file is stored after you restart the database or the computer (why do you need to delete it manually? the above steps only delete the logical relationship of the undo tablespace in ORACLE, that is, the association between data files in the data dictionary is deleted, and the associated data files are not automatically deleted ).
 
Drop tablespace undotbs1 including contents and datafiles;