1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/xxxx/undotbs201.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
2. issue the alter system command to point to the temporary tablespace
alter system set undo_tablespace = UNDOTBS2 ;
3. drop the original undo tablespace including datafile
drop tablespace "UNDOTBS" INCLUDING CONTENTS AND DATAFILES ;
4. recreate the original one using the original name ( optional and size according to your needs )
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oradata/xxxx/undotbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
5. Repeat step 2 to switch back to the original named tablespace
6. drop the tablespace created in step 1 using the syntax in step 3
Subscribe to:
Post Comments (Atom)
1 comment:
Even after altering the system to point to the new undo tablespace, I cannot issue the drop command on the original tablespace because it's "in use". Even starting and stopping the database doesn't change that fact.
Post a Comment