Re: SQL query to determine the users of undo tablespace

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Fri, 06 Apr 2007 06:06:36 GMT
Message-Id: <pan.2007.04.06.06.06.36@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:

QL> delete from scott.emp;

14 rows deleted.

SQL> select ubafil from v$transaction;

    UBAFIL

---

         2

SQL> select tablespace_name from dba_data_files where file_id=2;

TABLESPACE_NAME

---

UNDOTBS1 You can get it all from V$TRANSACTION and DBA_DATA_FILES.

-- 
http://www.mladen-gogala.com

Received on Fri Apr 06 2007 - 01:06:36 CDT