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.
I already tried setting undo_retention=1 and restarting the DB. But doesnot work.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> select * from dba_tablespaces where tablespace_name='UNDOTBS';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
UNDOTBS 4096 65536 1
2147483645 65536 ONLINE UNDO LOGGING LOCAL
SYSTEM NO MANUAL
SQL> select * from dba_data_files where tablespace_name='UNDOTBS';
What is your undo tablespace now?
You have not changed your undo tablespace as suggested before by one Pando and others.
You are still using UNDOTBS1 as your undo tablespace.
You have not executed the queries correctly.
I requested information for UNDOTBS1 but you have executed for UNDOTBS
Make the UNDOTBS2 or any other newly created tablespace as
current undo tablespace and make your SPFILE also recognise by using scope=both.
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:
Step 1:
CREATE UNDO
TABLESPACE "UNDOTBS1"
DATAFILE 'D:\UNDOTBS\UNDOTBS05.DBF' SIZE 200M
REUSE AUTOEXTEND
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.
Reply With Quote
Bookmarks