Home » RDBMS Server » Server Administration » Undo tablespace (Oracle 11.2.0.2.0,Solaris)
Undo tablespace [message #547879] Sat, 17 March 2012 13:24 Go to next message
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,
Re: Undo tablespace [message #547881 is a reply to message #547879] Sat, 17 March 2012 13:36 Go to previous messageGo to next message
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;

Re: Undo tablespace [message #547883 is a reply to message #547881] Sat, 17 March 2012 15:15 Go to previous messageGo to next message
John Watson
Messages: 8922
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
Re: Undo tablespace [message #547884 is a reply to message #547883] Sun, 18 March 2012 00:35 Go to previous messageGo to next message
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
Re: Undo tablespace [message #547885 is a reply to message #547884] Sun, 18 March 2012 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Undo tablespace [message #547887 is a reply to message #547885] Sun, 18 March 2012 03:47 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Undo tablespace [message #547891 is a reply to message #547887] Sun, 18 March 2012 04:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: too many archive log generation
Next Topic: session management
Goto Forum:
  


Current Time: Thu Mar 28 12:38:03 CDT 2024