This discussion is archived
3 Replies Latest reply: Oct 8, 2012 8:28 PM by Hemant K Chitale RSS

getting the undo tablespace use by session - level

redologger Newbie
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 Guru
    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.
  • 2. Re: getting the undo tablespace use by session - level
    redologger Newbie
    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.
  • 3. Re: getting the undo tablespace use by session - level
    Hemant K Chitale Oracle ACE
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points