Home » RDBMS Server » Backup & Recovery » Able to use undo tablespace after the datafile has been dropped
Able to use undo tablespace after the datafile has been dropped [message #309189] Wed, 26 March 2008 14:44 Go to next message
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

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

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

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 ?
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 Go to previous message
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.

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


Current Time: Sat Dec 10 04:51:33 CST 2016

Total time taken to generate the page: 0.08919 seconds