Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL query to determine the users of undo tablespace

Re: SQL query to determine the users of undo tablespace

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 6 Apr 2007 17:20:13 +0200
Message-ID: <4616652b$0$441$426a74cc@news.free.fr>

"Mladen Gogala" <mgogala.SPAM_ME.NOT_at_verizon.net> a écrit dans le message de news: pan.2007.04.06.06.06.36_at_verizon.net...
| On Fri, 06 Apr 2007 06:54:49 +0200, Michel Cadot wrote:
|
| > Yes, rollback segment names are meaningless but the question was about
| > undo tablespace, so I join with dba_rollback_segs to get undo tablespace
| > name of used rollback/undo segments. It was just to complete the answer
| > used blocks/tablespace/user.
|
| I was under the impression that you can only have one undo tablespace
| per instance? Nevertheless, V$TRANSACTION also has UBAFIL column
| which contains the file for the undo block:

Yes, thanks, I forgot this column.
I used to use XID columns and not UBA ones.

Here's an example where you have 2 undo tablespaces:

SQL> select tablespace_name from dba_data_files   2 where file_id in (select ubafil from v$transaction); Tablespace



UNDOTBS
UNDOTBS2 2 rows selected.

You can switch from one tablespace to othe another when transactions are active. New transactions go to the new undo tablespace when old ones go on on the previous one.

Regards
Michel Cadot Received on Fri Apr 06 2007 - 10:20:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US