I checked the spfile and init.ora.
The undo_management parameter is set to AUTO.
In spfile the undo_tablespace was pointing to my new tablespace while in init.ora it was pointing to my old one. I changed it, restarted the db.
Checked the status in dba_tablespaces. The old one is marked offline there.
But still I get the same error if I try to drop it. It doesnot allow me to drop that active rollback sagment also!
Aaha, - Mr davey23uk - Didn't you notice the **.....** note in my first post ?
Please stay away..
The below details might help us.
show parameter undo
select * from dba_tablespaces where tablespace_name='UNDOTBS1';
select * from dba_data_files where tablespace_name='UNDOTBS1';
SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;
Make sure the undo tablespace is set to undotbs2
alter system set undo_retention=50 scope=both;
--make sure the undotbs1 is online.
Do restart the database two times.(Normal)
try drop now.
If not post the result for the commands in the previous post.
Increase the retention later.
No no. Its a confusion. See - My original undo tablespace was UNDOTBS.
And the one i created newly is - UNDOTBS1. Since I need to drop UNDOTBS (not UNDOTBS1) , I executed your queries for UNDOTBS. If you have doubt, following is what I did and documented as per Tim and Pando's advice:
DATAFILE 'D:\UNDOTBS\UNDOTBS05.DBF' SIZE 200M
ON NEXT 5120K MAXSIZE 16383M
Check in the Enterprise manager consol if the tablespace is created.
Step 2: Fire a COMMIT query to ensure that all transections are commited.
Step 3: Tell the system to use the new tablespace
ALTER SYSTEM SET undo_tablespace=UNDOTBS1;
Step 4: Restart the DB. Just for safty.
Step 5: Drop the table old space
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
Step 6: Restart the DB. Check if it opens properly. Fire some DDL query.
Step 7: Delete the old datafiles from the filesystem. If not sure about the filename check from the view dba_data_files.