getting the undo tablespace use by session - level

redologger Oct 8, 2012 7:22 PM

Currently Being Moderated

hi guys,

select
s.username  
,s.sid  
,s.serial#  
,s.osuser  
,s.logon_time  
,s.status  
,s.machine  
,t.used_ublk  
,t.used_ublk*16384/1024/1024 undo_usage_mb  
from v$session     s  
    ,v$transaction t  
where t.addr = s.taddr;

My question is do i have to multiple base on the parameter below?

SQL> show parameter block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
*db_block_size                        integer     8192*
db_file_multiblock_read_count        integer     128

in these case i should -> t.used_ublk*8192/1024/1024 undo_usage_mb ??

1. Re: getting the undo tablespace use by session - level

rp0428 Oct 8, 2012 7:29 PM (in response to redologger)

Currently Being Moderated

Please rephrase your question. It is not clear what you are asking.

If you are trying to calc the number of undo BYTES used then, yes, you need to multiply 'used_ublk' by the block size.

You are using 16384 but the parameter list is showing 8192.

Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session.
Report Abuse

Like (0)
2. Re: getting the undo tablespace use by session - level

redologger Oct 8, 2012 8:19 PM (in response to rp0428)

Currently Being Moderated

rp0428 wrote:
Please rephrase your question. It is not clear what you are asking.

If you are trying to calc the number of undo BYTES used then, yes, you need to multiply 'used_ublk' by the block size.

You are using 16384 but the parameter list is showing 8192.

Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session.
thanks for the confirmation regarding using the block size.

Not too sure what u are saying on the last part

" Your thread title says you want the data by session so you would need to sum the blocks or bytes and group by sid and serial# to get the total for each session."

i was trying to get the undo tablespace used by a session / SQL consuming the allocated undo tablespace space in situation where it is not committing at the appropriate time and bad coding. Just testing it out.

so my logic is base on the USED_UBLK column of the v$transaction, we can get how much undo space it is consuming from not committing.
Report Abuse

Like (0)
3. Re: getting the undo tablespace use by session - level

Hemant K Chitale Oct 8, 2012 8:28 PM (in response to redologger)

Currently Being Moderated

The Block Size of the UNDO Tablespace would be the default block size which is 8192 as indicated by the database instance parameter.

Hemant K Chitale
Report Abuse

Like (0)

Go to original post

Actions

Legend

Correct Answers - 10 points
Helpful Answers - 5 points