-
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..
Last edited by TomNJerry; 07-02-2005 at 03:34 AM.
-
Try this.
alter tablespace undotbs1 online;
drop tablespace undotbs1 including CONTENTS AND DATAFILES;
-
Hi engiri,
I had already tried that. Din't work
-
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';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
E:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF
2 UNDOTBS 6485442560 1583360 AVAILABLE
2 YES 8388608000 2048000 1280 6485377024 1583344
D:\UNDOTBS\UNDOTBS02.DBF
13 UNDOTBS 1048576000 256000 AVAILABLE
13 NO 0 0 0 1048510464 255984
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
D:\UNDOTBS\UNDOTBS03.DBF
14 UNDOTBS 1048576000 256000 AVAILABLE
14 NO 0 0 0 1048510464 255984
D:\UNDOTBS\UNDOTBS04.DBF
15 UNDOTBS 1048576000 256000 AVAILABLE
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
15 NO 0 0 0 1048510464 255984
SQL> SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;
STATUS RELATIVE_FNO TABLESPACE_NAME COUNT(*)
---------------- ------------ ------------------------------ ----------
OFFLINE 2 UNDOTBS 4
ONLINE 1 SYSTEM 1
ONLINE 16 UNDOTBS1 10
PARTLY AVAILABLE 2 UNDOTBS 1
If you notice in the last query one rollback segment is partly available, which
is not allowing to drop that tablespace. So I tried this:
SQL> alter session set undo_suppress_errors=TRUE;
Session altered.
SQL> alter rollback segment "_SYSSMU6$" offline;
Rollback segment altered.
Then tried dropping the tablespace; But in vain.
Last edited by TomNJerry; 07-02-2005 at 10:23 AM.
-
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.
Revert with details.
-
fine, ill stay away. I know the answer to your problem, but you are obviously so intelligient and knowledgable you can figure it out yourself
-
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.
Step 8: Restart the DB.
I got stucked in step 5
Last edited by TomNJerry; 07-02-2005 at 08:52 AM.
-
I completely disagree with steps 4, 6 and 8. There is no reason to restart the db when switching undo tablespaces.
If the old one doesn't free up (drop), just give it some time. Don't panic. You may need to wait for it to age out the data (undo_retention seconds).
Step 7 - I thought 9i deleted the os file too? humm...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|