In this example, the current UNDO tablespace is UNDOTBS1. We will create a new UNDO tablespace named UNDOTBS2 and switch to it. Then, we will drop the previously used UNDO tablespace (UNDOTBS1).
-- 1. Check the current UNDO tablespace being used.
SHOW PARAMETER UNDO_TABLESPACE
-- 2. Create a new UNDO tbsp. Size it properlySQL> SHOW PARAMETER UNDO_TABLESPACE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/ORCL10G/undotbs02.dbf' SIZE 500M;
-- 3. Switch the undo_tablespace to the newly created.SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/ORCL10G/undotbs02.dbf' SIZE 500M;
Tablespace created.
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
-- 4. Drop the previous UNDO tablespace.SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
System altered.
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Note: If the current UNDO tablespace is still being used, you will encounter the below error:SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Resolution: You could NOT drop the tablespace until all transactions using it were ended. You'd had to wait and then retry the operation.SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
To check the status of UNDO:
select count(segment_name),sum(bytes/1024/1024),status from dba_undo_extents group by status;
Active: Extents that are currently in user
Unexpired: This represents the extents which are required to satisfy the time specified by undo_retention initialization parameter.
Expired: These are extents which are not being used by transaction and have crossed the time specified by undo_retention.
To immediately release all used extent, you might consider restarting the database!
-- 5. Verify that your database is now using the newly created UNDO tablespace.
SHOW PARAMETER UNDO_TABLESPACE
SQL> SHOW PARAMETER UNDO_TABLESPACE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2