How to Resize an Undo tablespace in Oracle

How to Resize an Undo tablespace in Oracle

Check current undo tablespace

connect as sysdba 

and do 

SQL> SHOW parameter UNDO_TABLESPACE

undo_tablespace string UNDOTBS1

To resize an Undo tablespace we have  to switch to a new Undo tablespace and drop the old Undo tablespace.

Steps

1. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> create undo tablespace UNDOTBS2 datafile '+TEST1' size 1000M;

2. Switch to the new Undo tablespace:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

3. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. 

We may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

4. If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

5. Verify and then drop old tablespace:

SQL> drop tablespace [tablespace_name] including contents and datafiles;

For example:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Resize the existing undo datafile

1. Resize the existing undo datafile:

col T_NAME for a23

col FILE_NAME for a65

select tablespace_name T_NAME,file_name, bytes/1024/1024 MB from dba_data_files where tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') order by file_name;

alter database datafile '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' resize [SIZE]M;

For example:

SQL> alter database datafile '+TEST1/dev/undo/undo_ts1.2324242.dbf' resize 1500M;

Or

2. Add a new datafile:

SQL> alter tablespace [UNDO tbs name] ADD DATAFILE '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' size 20M;

For example:

SQL> alter tablespace UNDOTBS1 ADD DATAFILE '+TEST1' size 20M;

If you like please follow and comment