How to deal with the large data file corresponding to the Oracle UNDO tablespace

1. view 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'

Data File:/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 until 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 view 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;