Undo TableSpace Resize (Recreate & Drop)
The resize command can be used to shrink the Undo TableSpace, however most of the time it isn’t that useful.
|
1 |
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/smp/UNDOTBS02.dbf' RESIZE 200; |
An alternative to the resize command is to create an undo tablespace of the required size and delete the old tablespace.
Undo TableSpace information.
|
1 |
SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace'); |
1. Create a new UNDO tablespace
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/smp/UNDOTBS02.dbf' SIZE 100M; alternatively CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 3G |
2. Modify the database parameter to use the new UNDO tablespace
|
1 |
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH; |
3. Drop the previous tablespace
|
1 |
drop tablespace undotbs1 including contents and datafiles; |
|
1 |
host ls -l /u01/app/oracle/oradata/smp/ |
The resulting alert.log entry: “Undo Tablespace X moved to Pending Switch-Out state”
A query to retrieve: Rollback Segment Number, Rollback Segment Name, Rollback Segment Status, Undo TableSpace Name, Transaction State Object Address, Session Identifier, Session Serial Number, Username to Run the Transaction, Program, Machine ID and the Operating System details.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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'; Ex: USN NAME STATUS TABLESPACE_NAME ADDR SID SERIAL# USERNAME PROGRAM --- --------- --------------- --------------- -------- ---- -------- -------- ----------- 5 _SYSSMU5$ PENDING OFFLINE UNDOTBS1 1477F404 9 2976 SYS sqlplus.exe MACHINE OS_USER ---------------------- -------------------- IDC-ORACLEbsriniva-pc bsriniva-pcbsriniva |
|
1 |
Alter system kill session ‘sid numarasi,serial# numarasi’; |
Rollback segments
|
1 2 3 4 5 |
select segment_name , status from dba_rollback_segs |
|
1 |
alter system set undo_retention=500 scope=memory; |
|
1 2 3 4 5 6 7 8 9 10 11 |
select tablespace_name , status , count(*) as HOW_MANY from dba_undo_extents group by tablespace_name , status |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set lines 100 pages 999 col username format a15 col command format a20 select ses.username -- Session username , substr(ses.program, 1, 19) command , tra.used_ublk from v$session ses , v$transaction tra where ses.saddr = tra.ses_addr |
