OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Undo tablespace (Oracle 11.2.0.2.0,Solaris)

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

Undo tablespace [message #547879]

Sat, 17 March 2012 13:24

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Hi,

How to know which undo tablespace is currently being used in database from the below result.

SQL> select tablespace_name from dba_data_files group by tablespace_name;

TABLESPACE_NAME
------------------------------
INDEX1
SYSAUX
UNDOTBS1
USERS
SYSTEM
UNDOTBS2
DATA1

7 rows selected.

Regards,

Report message to a moderator

Re: Undo tablespace [message #547881 is a reply to message #547879]

Sat, 17 March 2012 13:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

why do you have two UNDO tablespaces?

SELECT TABLESPACE_NAME, MAX(BLOCK_ID) FROM DBA_EXTENTS WHERE TABLESPACE_NAME LIKE 'UNDO%' GROUP BY TABLESPACE_NAME;

Report message to a moderator

Re: Undo tablespace [message #547883 is a reply to message #547881]

Sat, 17 March 2012 15:15

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

If the database is a two node RAC, then two undo tablespaces would be normal Smile

@Jack14, you can't tell from the tablespace name what type of tablespace it is: you should project the CONTENTS column too. And to answer your question,

show parameter undo_tablespace

Report message to a moderator

Re: Undo tablespace [message #547884 is a reply to message #547883]

Sun, 18 March 2012 00:35

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

John,

What you said was correct .I verified each node of database and found that each node has its own undo tablespace.
Thank you for your answer.

Actually it was my mistake because i should have mentioned that i am using RAC database. Smile

John,

I have one more question. What if we use a single (ie.common) undo tablespace for a two node RAC database?

Regards

Report message to a moderator

Re: Undo tablespace [message #547885 is a reply to message #547884]

Sun, 18 March 2012 02:06

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

What if we use a single (ie.common) undo tablespace for a two node RAC database?

You can't.

Regards
Michel

Report message to a moderator

Re: Undo tablespace [message #547887 is a reply to message #547885]

Sun, 18 March 2012 03:47

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Just for completeness, with 11.2 undo tablespaces are in efect "publc": if the parameter is not set, then each instance takes the first currently unused undo tablespace. This makes policy managed databases easier to manage. If there is not an unused undo tablespace, it used SYSTEM. It is some time since I tested this, but I think more that one instance can create undo segments in SYSTEM if necessary.

Report message to a moderator

Re: Undo tablespace [message #547891 is a reply to message #547887]

Sun, 18 March 2012 04:40

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if the parameter is not set, then each instance takes the first currently unused undo tablespace

But no undo tablespace is shared at one time, each undo tablespace is used by at most one instance, isn't it?

Quote:

If there is not an unused undo tablespace, it used SYSTEM. It is some time since I tested this, but I think more that one instance can create undo segments in SYSTEM if necessary.

I don't know in 11.2 but it was not the case in previous versions: SYSTEM tablespace could only contain SYSTEM rollback segment (and deferred RBS in very special cases) and this SYSTEM rollback segment can only be used by SYS during recursive management operations.

Regards
Michel

Report message to a moderator

Previous Topic:	too many archive log generation
Next Topic:	session management

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 20:50:54 CST 2025