How to deal with the problem that the data file corresponding to the Oracle UNDO tablespace is too large

How to deal with the problem that the data file corresponding to Oracle's UNDO tablespace is too large? fault symptom: Check the df-g space in AIX and check that the corresponding database's undo tablespace has reached more than 90%.

How to deal with the problem that the data file corresponding to Oracle's UNDO tablespace is too large? fault symptom: Check the df-g space in AIX and check that the corresponding database's undo tablespace has reached more than 90%.

Fault description: Check the df-g space on AIX and check that the undo tablespace of the corresponding database has reached more than 90%. The following process the problem of too large data files in the tablespace.

-- 1 check the tablespace size and maximum value of undo.
Select t. file_name, t. tablespace_name,
T. bytes/1024/1024/1024 "GB", t. maxbytes/1024/1024/1024 "Max GB"
From dba_data_files t where t. tablespace_name = 'undotbs1'

-- The data file is/Oracle/oradata/undo/undotbs01.dbf.

-- 2 create a new undo tablespace to replace the original undo tablespace.
Create undo tablespace UNDOTBS2
Datafile '/oracle/oradata/log/undotbs02.dbf'
Size 10 M autoextend on maxsize unlimited;

-- 3. Set the new undo tablespace to the undo tablespace of the database.
Alter system set undo_tablespace = UNDOTBS2 scope = both;

-- 4 verify the undo tablespace of the database again
Show parameter undo_tablespace

-- 5 wait for the original UNDO tablespace UNDOTBS1 is OFFLINE;

SELECT r. status "Status ",
R. segment_name "Name ",
R. tablespace_name "Tablespace ",
S. extents "Extents ",
TO_CHAR (s. bytes/1024/1024), '2017. 000') "Size"
FROM sys. dba_rollback_segs r, sys. dba_segments s
WHERE r. segment_name = s. segment_name
AND s. segment_type IN ('rollback', 'type2 UNDO ')
And r. tablespace_name = 'undotbs1' and status = 'online'

If there is an online object on it, you can query the sid of a specific object, serial #

-- 5.1 check what is using this rollback segment
SELECT r. NAME, s. sid, s. serial # Serial,
S. username, s. machine,
T. start_time, t. status,
T. used_ublk,
Substr (s. program, 1, 15) "operate"
FROM v $ session s, v $ transaction t, v $ rollname r, v $ rollstat g
WHERE t. addr = s. taddr
AND t. xidusn = r. usn
AND r. usn = g. usn
Order by t. used_ublk desc;

-- For example, the object is sid 474, serial 6794

-- 5.2 identify the specific SQL statement based on the sid
Select SQL _text from v $ session a, v $ sqltext_with_newlines B
Where DECODE (a. SQL _hash_value, 0, prev_hash_value, SQL _hash_value) = B. hash_value
And a. sid = & sid order by piece

If the SQL statement is not important, kill the session directly.

-- 5.3 kill session
Alter system kill session '2017 4 ';

-- 5.4 deletion of the original undo tablespace and its System Data
Drop tablespace UNDOTBS1 including contents and datafiles;
(In the AIX system, although the data file of the undo tablespace corresponding to the system has been deleted, the system space cannot be released when df-g is used for viewing.
This is mainly because a process in Oracle accesses this file. You can kill Oracle to access the process, or restart the database to release the system space .)


-- 6 create a new UNDOTBS1 tablespace
Create undo tablespace UNDOTBS1
Datafile '/oracle/oradata/undo/undotbs01.dbf'
Size 10 M autoextend on maxsize 12G;

-- 7 switch back to UNTOTBS1
Alter system set undo_tablespace = UNDOTBS1 scope = both;

-- 8 wait for the UNDO tablespace UNDOTBS2 is OFFLINE;
SELECT r. status "Status ",
R. segment_name "Name ",
R. tablespace_name "Tablespace ",
S. extents "Extents ",
TO_CHAR (s. bytes/1024/1024), '2017. 000') "Size"
FROM sys. dba_rollback_segs r, sys. dba_segments s
WHERE r. segment_name = s. segment_name
AND s. segment_type IN ('rollback', 'type2 UNDO ')
And r. tablespace_name = 'undotbs2'
Order by 5 DESC;

-- 9 Delete
Drop tablespace UNDOTBS2 including contents and datafiles;