Oracle undo tablespace full solution

Oracle undo tablespace is full. 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 database UNDO tablespace and the location where the data files are stored. select file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name like 'undotbs % '; 5. check the usage of rollback segments, which user is using resources of the rollback segments, and if there is a user, it is best to change the time (especially in the production environment ). 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; the query result can be deleted if it is null. 6. check the UNDO Segment status; 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 SHRINKS8 0 0.00017547607421875 3.11521148681641 7003 0 0.00023651123046875 3.22954559326172 0 6320 0.00035858154296875 01 0 0.00035858154296875 0.00206756591796875 3.04867553710938 0 92010 75 0.648170471191406 8197 0.00231170654296875 3.94835662841797 7304 0.00304412841796875 2.00011444091797 65111 0.00695037841796875 2.26921844482422 7409 0.00792694091796875 2.07530975341797 7736 0.00792694091796875 1.31906890869141 7752 0.00890350341796875 3.13677215576172 6995 1.96833801269531 3.99906921386719 267 This restored tablespace still has 12 target. 7. create a new UNDO tablespace and set automatic expansion parameters. create undo tablespace undotbs2 datafile '/u02/pnrdb/undotbs2_01.dbf' size 100 m reuse autoextend on next 100 m maxsize unlimited; 8. switch the UNDO tablespace to the new UNDO tablespace and dynamically change the spfile configuration file. alter system set undo_tablespace = undotbs2 scope = both; 9. verify the UNDO tablespace SQL> show parameter undoNAME TYPE VALUE -------------------------------------- ----------- -------------- undo_management string AUTOundo of the current database _ Retention integer 900undo_tablespace string UNDOTBS29. wait for all UNDO segments in the original UNDO tablespace OFFLINE; select usn, xacts, status, rssize/1024/1024, hwmsize/1024/1024, shrinks from v $ rollstat order by rssize; 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_seg S t; SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID %$ UNDOTBS1 1 %$ UNDOTBS1 2 %$ UNDOTBS1 3 %$ UNDOTBS1 4 %$ %5 %$ %6 %$ UNDOTBS1 7 %$ UNDOTBS1 8 %$ UNDOTBS1 % $ UNDOTBS1 10 OFFLINE_SYSSMU11 $ UNDOTBS1 11 OFFLINE_SYSSMU12 $ UNDOTBS1 12 OFFLINE_SYSSMU 13 $ UNDOTBS1 13 Response $ UNDOTBS1 14 response $ UNDOTBS1 15 response $ UNDOTBS1 16 response $ UNDOTBS1 17 response $ UNDOTBS1 18 response $ UNDOTBS1 19 Response $ UNDOTBS1 20 response $ UNDOTBS1 21 response $ UNDOTBS1 OFFLINE_SYSSMU23 $ UNDOTBS1 23 OFFLINE_SYSSMU24 $ UNDOTBS1 24 OFFLINE_SYSSMU25 $ UNDOTBS1 25 OFFLINE_SYSSMU26 $ UNDOTB S1 26 response $ UNDOTBS1 27 response $ UNDOTBS1 28 response $ UNDOTBS1 29 Response $ UNDOTBS1 30 response $ UNDOTBS1 31 response $ UNDOTBS1 32 response $ UNDOTBS1 33 response $ UNDOTBS1 34 response $ UNDOTBS1 35 OFFLINE above the rollback segments corresponding to the UNDOTBS1 tablespace are OFFLINE 10. to $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora, check whether the following content has changed: # cat $ ORACLE_HOME/dbs/initddpt Est. 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; drop tablespace undotbs1 including contents and datafiles; 12. undo data files are released at the OS level; run lsof | grep/u02/pnrdb/undotbs01.dbf lsof | grep/u02/pnrdb/undotbs01.dbf | awk '{printf "kill-9" $2 "\ n "}'