3 Replies Latest reply: Oct 8, 2012 10:28 PM by Hemant K Chitale RSS

    getting the undo tablespace use by session - level

    redologger
      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
          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
            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
              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