The resize command can be used to shrink the Undo TableSpace, however most of the time it isn’t that useful.
\n[crayon]ALTER DATABASE DATAFILE ‘\/u01\/app\/oracle\/oradata\/smp\/UNDOTBS02.dbf’ RESIZE 200;<\/p>\n
[\/crayon]
\nAn alternative to the resize command is to create an undo tablespace of the required size and delete the old tablespace.<\/p>\n
Undo TableSpace information.
\n[crayon]SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,’undo_tablespace’);<\/p>\n
[\/crayon] DATAFILE ‘\/u01\/app\/oracle\/oradata\/smp\/UNDOTBS02.dbf’ SIZE 100M;<\/p>\n alternatively<\/p>\n CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE<\/p>\n ‘\/oradata\/undotbs02.dbf’<\/p>\n SIZE 2G<\/p>\n AUTOEXTEND ON<\/p>\n NEXT 200M<\/p>\n MAXSIZE 3G[\/crayon] [\/crayon] [\/crayon] [\/crayon] 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. a.usn,<\/p>\n a.name,<\/p>\n b.status,<\/p>\n c.tablespace_name,<\/p>\n d.addr,<\/p>\n e.sid,<\/p>\n e.serial#,<\/p>\n e.username,<\/p>\n e.program,<\/p>\n e.machine,<\/p>\n e.osuser<\/p>\n FROM<\/p>\n v$rollname a,<\/p>\n v$rollstat b,<\/p>\n dba_rollback_segs c,<\/p>\n v$transaction d,<\/p>\n v$session e<\/p>\n WHERE<\/p>\n a.usn=b.usn AND<\/p>\n a.name=c.segment_name AND<\/p>\n a.usn=d.xidusn AND<\/p>\n d.addr=e.taddr AND<\/p>\n b.status=’PENDING OFFLINE’;<\/p>\n Ex:<\/p>\n USN NAME STATUS TABLESPACE_NAME ADDR SID SERIAL# USERNAME PROGRAM<\/p>\n — ——— ————— ————— ——– —- ——– ——– ———–<\/p>\n 5 _SYSSMU5$ PENDING OFFLINE UNDOTBS1 1477F404 9 2976 SYS sqlplus.exe<\/p>\n MACHINE OS_USER<\/p>\n ———————- ——————–<\/p>\n IDC-ORACLEbsriniva-pc bsriniva-pcbsriniva[\/crayon] [\/crayon] , status<\/p>\n from dba_rollback_segs<\/p>\n [\/crayon] [\/crayon] , status<\/p>\n , count(*) as HOW_MANY<\/p>\n from dba_undo_extents<\/p>\n group by tablespace_name<\/p>\n , status[\/crayon] col username format a15<\/p>\n col command format a20<\/p>\n select ses.username — Session username<\/p>\n , substr(ses.program, 1, 19) command<\/p>\n , tra.used_ublk<\/p>\n from v$session ses<\/p>\n , v$transaction tra<\/p>\n where ses.saddr = tra.ses_addr[\/crayon]<\/p>\n","protected":false},"excerpt":{"rendered":" Undo TableSpace Resize (Recreate & Drop)<\/p>\n","protected":false},"author":1,"featured_media":3701,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[350],"tags":[345],"class_list":["post-3168","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-miscellaneous-en","tag-oracle"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/3168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=3168"}],"version-history":[{"count":0,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/3168\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=3168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=3168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=3168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n1.<\/strong> Create a new UNDO tablespace
\n[crayon]CREATE UNDO TABLESPACE UNDOTBS2<\/p>\n
\n2.<\/strong> Modify the database parameter to use the new UNDO tablespace
\n[crayon]ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;<\/p>\n
\n3.<\/strong> Drop the previous tablespace
\n[crayon]drop tablespace undotbs1 including contents and datafiles;<\/p>\n
\n[crayon]host ls -l \/u01\/app\/oracle\/oradata\/smp\/<\/p>\n
\nThe resulting alert.log entry: “Undo Tablespace X moved to Pending Switch-Out state”<\/p>\n
\n[crayon]SELECT<\/p>\n
\n[crayon]Alter system kill session \u2018sid numarasi,serial# numarasi\u2019;<\/p>\n
\nRollback segments
\n[crayon]select segment_name<\/p>\n
\n[crayon]alter system set undo_retention=500 scope=memory;<\/p>\n
\n[crayon]select tablespace_name<\/p>\n
\n[crayon]set lines 100 pages 999<\/p>\n