3 Replies Latest reply on Oct 9, 2012 3:28 AM by Hemant K Chitale

    getting the undo tablespace use by session - level

    redologger
    redologger Oct 9, 2012 2:22 AM
      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 ??
      • 297 Views
      • Tags:
        • 1. Re: getting the undo tablespace use by session - level
          rp0428
          rp0428 Oct 9, 2012 2:29 AM (in response to redologger)
          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.
          1 person found this helpful
          • 2. Re: getting the undo tablespace use by session - level
            redologger
            redologger Oct 9, 2012 3:19 AM (in response to rp0428)
            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
              Hemant K Chitale Oct 9, 2012 3:28 AM (in response to redologger)
              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
              1 person found this helpful