Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL query to determine the users of undo tablespace
"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
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