Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Monday, April 16, 2007

Resizing Undo Tablespaces

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
_SYSSMU271$|ONLINE|2|122880|122880|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

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 
'/datac3/oradata/andlp2/undotbs1_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M,
'/datac6/oradata/andlp2/undotbs1_02.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M,
'/datac7/oradata/andlp2/undotbs1_03.dbf' SIZE 100M AUTOEXTEND OFF
ONLINE
BLOCKSIZE 8K;

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 ;     

3 comments:

Tom said...

Thanks, this was helpful.

carcasco said...

Thanks for your mail. It is very useful. I would like to translate your post to my blog in Spanish, can I do?

I will put your blog as a reference.

Ural Ural said...

Dear Carcasco,

I am happy that this article helped you. Of course you can translate it to Spanish and use it as a reference.

Regards,
Ural