shrinking (resizing) undo tablespace in oracle database

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs_2 DATAFILE ‘/orad001/data_files/undotbs2_02.dbf’ size 10000M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs_2;

3. Drop the old UNDO tablespace

DROP TABLESPACE UNDOTBS1 including contents;

****** if you face an error that you can’t drop the undo tablespace then you need to check the sessions that requires (commit or rollback).

you may face the error “Undo Tablespace X moved to Pending Switch-Out state”

To Fix this use the following query to check the session,oracleuser, program that requires either a commit transaction or a rollback.

**********************
SELECT
a.usn,a.name,b.status,
c.tablespace_name,d.addr,e.sid,e.serial#,
e.username,e.program,e.machine,e.osuser
FROM
v$rollname a,v$rollstat b,
dba_rollback_segs c,v$transaction d,
v$session e
WHERE
a.usn=b.usn AND a.name=c.segment_name AND
a.usn=d.xidusn AND d.addr=e.taddr AND
b.status=’PENDING OFFLINE’;
**************************

4. do verification using this command:

SQL> show parameter undo_tablespace


********************************************************************************

Undo Segments

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

Check the overall status for undos
********************************************************************************
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

Undo Blocks per Second
********************************************************************************
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

Optimal Undo Retention and  Calculate Needed UNDO Size for given Database Activity
********************************************************************************
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
       ,(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024)       "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';