SQL query to determine the users of undo tablespace

[yaru22]

How do I determine which users are using undo tablespace in an Oracle
9.2 database?

Thanks.

[unknown]

"yaru22" <***@gmail.com> a écrit dans le message de news: ***@d57g2000hsg.googlegroups.com...
| How do I determine which users are using undo tablespace in an Oracle
| 9.2 database?
|
| Thanks.
|

Join dba_segments (or dba_rollback_segs), v$rollstat, v$rollname, v$session and v$transaction.

Regards
Michel

[Mladen Gogala]

determine which users are using undo tablespace in an Oracle | 9.2
database?
|
| Thanks.
|
Join dba_segments (or dba_rollback_segs), v$rollstat, v$rollname,
v$session and v$transaction.

There is an article about that on JL Consultancy cooperative FAQ,
written by Mark Powell:
http://www.jlcomp.demon.co.uk/faq/active_rollback.html

The article is obsolete and intended only for oracle7 & 8. As you know,
with Oracle9i, rollback segments are hidden in the UNDO tablespace, so
the segment names are meaningless. Also, in 9i and above, v$transaction
shows you how many undo blocks are being used by the transaction
("used_ublk" column). I have somewhat simpler query of my own, which is
not as detailed as Mark's:

SELECT sess.username,
sess.sid,sess.serial# serial,p.spid "System PID",
sess.program,sess.osuser,sess.machine,
t.used_ublk "Undo blocks",t.status "Trans. Status",
to_char(logon_time,'MM/DD/YYYY HH24:MI') "Logged In"
FROM v$session sess,v$transaction t,v$process p
WHERE sess.saddr=t.ses_addr and
sess.paddr=p.addr and
ORDER BY t.status,t.used_ublk desc;

[unknown]

"Mladen Gogala" <mgogala.SPAM-***@verizon.net> a écrit dans le message de news: ***@verizon.net...
| On Thu, 05 Apr 2007 19:02:52 +0200, Michel Cadot wrote:
|
| > "yaru22" <***@gmail.com> a écrit dans le message de news:
| > ***@d57g2000hsg.googlegroups.com... | How do I
| > determine which users are using undo tablespace in an Oracle | 9.2
| > database?
| > |
| > | Thanks.
| > |
| >
| > Join dba_segments (or dba_rollback_segs), v$rollstat, v$rollname,
| > v$session and v$transaction.
|
| There is an article about that on JL Consultancy cooperative FAQ,
| written by Mark Powell:
| http://www.jlcomp.demon.co.uk/faq/active_rollback.html
|
| The article is obsolete and intended only for oracle7 & 8. As you know,
| with Oracle9i, rollback segments are hidden in the UNDO tablespace, so
| the segment names are meaningless. Also, in 9i and above, v$transaction
| shows you how many undo blocks are being used by the transaction
| ("used_ublk" column). I have somewhat simpler query of my own, which is
| not as detailed as Mark's:
|
| SELECT sess.username,
| sess.sid,sess.serial# serial,p.spid "System PID",
| sess.program,sess.osuser,sess.machine,
| t.used_ublk "Undo blocks",t.status "Trans. Status",
| to_char(logon_time,'MM/DD/YYYY HH24:MI') "Logged In"
| FROM v$session sess,v$transaction t,v$process p
| WHERE sess.saddr=t.ses_addr and
| sess.paddr=p.addr and
| ORDER BY t.status,t.used_ublk desc;
|
|
| --
| http://www.mladen-gogala.com

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.

(There may be several undo tablespaces in use, if you are in the
process of changing it.)

Regards
Michel Cadot

[Mladen Gogala]

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.

[unknown]

"Mladen Gogala" <***@verizon.net> a écrit dans le message de news: ***@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

[Mladen Gogala]

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.

Hmmm, interesting. I guess that the goal is to allocate separate UNDO
tablespace for big batches? Unfortunately, that solution suffers from the
same problems as the rollback segment one: it works as long as the "big
batch" doesn't commit. After that, it uses the new undo segments. My
phylosophy is that disks are cheap, especially if somebody else is paying
for them, ao I allocate as much space for the UNDO tablespace as needed.

[unknown]

"Mladen Gogala" <mgogala.SPAM-***@verizon.net> a écrit dans le message de news: ***@verizon.net...
| On Fri, 06 Apr 2007 17:20:13 +0200, Michel Cadot wrote:
|
| > 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.
|
| Hmmm, interesting. I guess that the goal is to allocate separate UNDO
| tablespace for big batches? Unfortunately, that solution suffers from the
| same problems as the rollback segment one: it works as long as the "big
| batch" doesn't commit. After that, it uses the new undo segments. My
| phylosophy is that disks are cheap, especially if somebody else is paying
| for them, ao I allocate as much space for the UNDO tablespace as needed.
|
| --
| http://www.mladen-gogala.com

I had no purpose, I just explained what can be happened.
I never used it. Maybe it will be useful if you lose one file in the
current undo tablespace to switch to another one hoping you don't
have a transaction that goes to the unavailable file.
Or when you want to put your undo tablespace onto another
disk without shutting down the database.
There may be multiple reasons to do that.

Regards
Michel Cadot

[Matthias Hoys]

Post by Mladen Gogala

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.

Hmmm, interesting. I guess that the goal is to allocate separate UNDO
tablespace for big batches? Unfortunately, that solution suffers from the
same problems as the rollback segment one: it works as long as the "big
batch" doesn't commit. After that, it uses the new undo segments. My
phylosophy is that disks are cheap, especially if somebody else is paying
for them, ao I allocate as much space for the UNDO tablespace as needed.
--
http://www.mladen-gogala.com

True, disks are cheap, but if your SAN is full and you need a new storage
cabinet, FC switches and licenses, it's getting more expensive :-)

Matthias

[Fuzzy]

Post by Matthias Hoys

Post by Mladen Gogala

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.

Hmmm, interesting. I guess that the goal is to allocate separate UNDO
tablespace for big batches? Unfortunately, that solution suffers from the
same problems as the rollback segment one: it works as long as the "big
batch" doesn't commit. After that, it uses the new undo segments. My
phylosophy is that disks are cheap, especially if somebody else is paying
for them, ao I allocate as much space for the UNDO tablespace as needed.
--
http://www.mladen-gogala.com

True, disks are cheap, but if your SAN is full and you need a new storage
cabinet, FC switches and licenses, it's getting more expensive :-)
Matthias

Makes one wonder whether TEMP and UNDO tablespaces need to be on the
expensive disk.

/Hans

[joel garry]

Post by yaru22
How do I determine which users are using undo tablespace in an Oracle
9.2 database?
Thanks.

See metalink Note: 1039126.6 for one way. Google around, I'm sure
there are others.

(Mladen, you seem to have missed a line or two in your script)

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20070405/news_1b5google.html

[Mladen Gogala]

Post by joel garry
(Mladen, you seem to have missed a line or two in your script)

Not really, it gives me what I need.