OraFAQ Forum: Backup & Recovery » Able to use undo tablespace after the datafile has been dropped

Home » RDBMS Server » Backup & Recovery » Able to use undo tablespace after the datafile has been dropped

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Able to use undo tablespace after the datafile has been dropped [message #309189]

Wed, 26 March 2008 14:44

rolex.mp
Messages: 161
Registered: February 2007

Senior Member

I am using Oracle 10g release 2 database in Unix

I lost the undo datafile , so I restarted my db and issued this statement

Code: [Select all] [Show/ hide]

SQL> alter database datafile '/slot01/oracle/db/adnzn001/dbf/undotbs01.dbf' offline drop;

Database altered.
SQL> alter database open;

Database altered.

In this case where will the undo data be stored , since the database is using automatic undo management

Code: [Select all] [Show/ hide]

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      APPS_UNDO_TBS

When I tried dropping undo tablespace its throwing error

Code: [Select all] [Show/ hide]

SQL> drop tablespace APPS_UNDO_TBS;
drop tablespace APPS_UNDO_TBS
*
ERROR at line 1:
ORA-30013: undo tablespace 'APPS_UNDO_TBS' is currently in use

What does this mean ? where is the undo data stored since the datafile has been deleted from disk and also i dropped it using sql statement ?

Report message to a moderator

Re: Able to use undo tablespace after the datafile has been dropped [message #309316 is a reply to message #309189]

Thu, 27 March 2008 02:01

Rajabaskar Thangaraj
Messages: 13
Registered: March 2008
Location: CHENNAI

Junior Member

Now undo segments are stored in System tablespace.
we Create new undo tablespaces.
alter system set undo_tablespace=<new_undo_tablespace_name>;
Drop the old one.

Report message to a moderator

Previous Topic:	How to Recover redo01.log files
Next Topic:	Clone using RMAN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 23:02:33 CDT 2025