Drop And Recreate Undo Tablespace
==================================
1. Check the existing UNDO details:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
2. Create a new undo tablespace:
SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/oradata/ARCH/undotbs101.dbf' SIZE 500M AUTOEXTEND ON RETENTION GUARANTEE;
3. Update undo_tablespace parameter
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=BOTH;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
4.Drop the old undo tablespace
SQL> DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES;
-------------------------------------------------------------------------------------------------------------------
1. Check for the active sessions for rollback segment in old tablespace if drop unsuccessfull.
set pagesize 200
set lines 200
set long 999
col username for a9
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS3'
);
NAME STATUS USERNAME SID SERIAL#
------------------------------ --------------- --------- ---------- ----------
_SYSSMU10_46915872145$ PENDING OFFLINE DBACLASS 193 732119
5. Kill the active session(s) which is using old tablespace
SQL> alter system kill session '193,732119' immediate;
System altered.
No comments:
Post a Comment