Resizing Undo Tablespaces

Resizing Undo Tablespaces
Author
Ural Ural
Oracle DBA

How do you resize undo tablepaces ? This is how I do it in an Oracle 9i database.

Problem is the undo tablespace has grown up to the limit of its maxsize
and we need to compact it or just we want to make it smaller due to a space problem

--Find undo info
SELECT NAME, VALUE
FROM v$parameter
WHERE LOWER (NAME) LIKE '%undo%';

NAME|VALUE
undo_management|AUTO
undo_tablespace|UNDOTBS1


--Find undo tablespace datafile info
SELECT file_name, BYTES / 1024 / 1024 mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
ORDER BY file_name;
/*
FILE_NAME|MB
/datac3/oradata/andlp2/undotbs1_01.dbf|1793
/datac6/oradata/andlp2/undotbs1_02.dbf|235
/datac7/oradata/andlp2/undotbs1_03.dbf|1679
*/

--Execute the following query to determine who is using a particular UNDO or Rollback Segment:
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
--No rows returned

--Create a second temporary undo tablespace
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/datac7/oradata/andlp2/undotbs2_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M
ONLINE
BLOCKSIZE 8K;

--Set this new undo tablespace as undo tablespace of the database
ALTER SYSTEM SET undo_tablespace = UNDOTBS2;

--You can monitor the undo extents of all the undo tablespaces by using the following query
SELECT tablespace_name, segment_name, SUM (blocks), SUM (BYTES) / 1024
FROM dba_undo_extents
GROUP BY tablespace_name, segment_name;

--Since we are going to drop the old undo tablespace we need to know if any session is
--using any undo extent of this undo tablespace
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1'
/*
SID_SERIAL|ORAUSER|PROGRAM|UNDOSEG|Undo|TABLESPACE_NAME
615,998|USER1|runform30x@host1 (TNS interface)|_SYSSMU102$|8K|UNDOTBS1
*/

--You can get more info about the undo extent that is online (used)
SELECT *
FROM dba_rollback_segs
WHERE status = 'ONLINE'
AND tablespace_name = 'UNDOTBS1'
ORDER BY tablespace_name, segment_name
/*
SEGMENT_NAME|OWNER|TABLESPACE_NAME|SEGMENT_ID|FILE_ID|BLOCK_ID|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|STATUS|INSTANCE_NUM|RELATIVE_FNO
_SYSSMU102$|PUBLIC|UNDOTBS1|102|33|4473|131072||2|32765||ONLINE||33
*/

--Monitor the status of online undo extents
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS1'
AND segment_id = usn;
/*
SEGMENT_NAME|XACTS|STATUS
_SYSSMU102$|1|PENDING OFFLINE
*/
--See it is in "PENDING OFFLINE" status until the transaction that is using it ends.

--See all the rollback info
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU102$|PENDING OFFLINE|42|42065920|42065920|1
_SYSSMU262$|ONLINE|4|253952|253952|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|2|122880|122880|0
_SYSSMU271$|ONLINE|2|122880|122880|0
*/

--Find the active transactions using undo extents
SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
/*
USERNAME|XIDUSN|UBAFIL|UBABLK|USED_UBLK
MARDATA|102|30|153176|1
*/

/*
After all active transactions are committed, the status of Undo segment
_SYSSMU102$ is automatically switched from PENDING OFFLINE mode to OFFLINE mode.
PENDING OFFLINE mode does not allow any deletion process of tablespace.
You can delete the tablespace in OFFLINE mode.

If no segment is selected after you execute the SQL statement below,
it means the segment is already switched to OFFLINE mode.

SQL> SELECT SEGMENT_NAME, XACTS, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS_02'
AND SEGMENT_ID = USN;

If the segment is selected and XACTS points to zero (0), rollback segment
does not contain any pending transactions, and therefore,
the segment is switched to OFFLINE mode immediately
*/

--Wait for the active transaction to finish then monitor again
SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;


NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU262$|ONLINE|5|319488|319488|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|3|188416|188416|0


--As you can see all undo extents of the old undo tablespace UNDOTBS1 are dropped already
--now we can drop the tablespace

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;


Recreate the original configuration :
After that we can create again the original configuration, so recreate the old undo again
and switch to it


ALTER SYSTEM SET undo_tablespace = UNDOTBS1;

--See if there are any active undo extents of UNDOTBS2

SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU1$|ONLINE|2|122880|122880|0
_SYSSMU2$|ONLINE|2|122880|122880|0
_SYSSMU3$|ONLINE|2|122880|122880|0
_SYSSMU4$|ONLINE|2|122880|122880|0
_SYSSMU5$|ONLINE|2|122880|122880|0
_SYSSMU6$|ONLINE|2|122880|122880|0
_SYSSMU7$|ONLINE|2|122880|122880|0
_SYSSMU8$|ONLINE|2|122880|122880|0
_SYSSMU9$|ONLINE|2|122880|122880|0
_SYSSMU10$|ONLINE|2|122880|122880|0


--See if there is any rollback segments of UNDOTBS2

SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS2'
AND segment_id = usn;
--No rows returned

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;