OraFAQ Forum: General » Undo Tablespace

Home » Other » General » Undo Tablespace (10g)

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

Undo Tablespace [message #314849]

Fri, 18 April 2008 00:54

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi all,

i created new undo tablespace called 'UNDOTBS_02'. previously it was 'UNDOTBS_01' was created during database creation and the undo mode has been set to AUTO.then i decided to switch to new undo tablespace UNDOTBS_02 using the ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02; and the command was sucessful.

When i tried to drop the old undo tablespace UNDOTBS_01 it says the tablespace does not exists. but when i query the view dba_free_space it shows me the old tablespace UNDOTBS_01.
i dont want the old tablespace to exist in my database.

please let me know the steps to proceed further

Report message to a moderator

Re: Undo Tablespace [message #314858 is a reply to message #314849]

Fri, 18 April 2008 01:21

gopu_g
Messages: 54
Registered: March 2008
Location: mumbai

Member

hi

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries

Gopu Smile

Report message to a moderator

Re: Undo Tablespace [message #314868 is a reply to message #314849]

Fri, 18 April 2008 01:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Still randomly choosing the forum you create your topics?

I recommend you to ALWAYS post in "Homework, Training & Certification" one, this is most likely the best choice for your question.

Regards
Michel

Report message to a moderator

Re: Undo Tablespace [message #315050 is a reply to message #314849]

Fri, 18 April 2008 14:06

varu123
Messages: 754
Registered: October 2007

Senior Member

Quote:

When i tried to drop the old undo tablespace UNDOTBS_01 it says the tablespace does not exist

Do you see the tablespace in dba_tablespaces?
Do you see the rollback segments associated with that tablespace in dba_rollback_segs along with status?

Report message to a moderator

Previous Topic:	Packet Sniffer Picking Up Weird Characters
Next Topic:	http://tahiti.oracle.com/

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:28:40 CDT 2024