Sunday, 27 November 2011

Oracle uncommited transactions 




Oracle 
uncommited transactions 

  ..




uncommited transactions






SET LINESIZE 200 PAGESIZE 300
COLUMN sid                    FORMAT 99999           HEADING 'SID'
COLUMN serial_id              FORMAT 99999999        HEADING 'Serial ID'
COLUMN session_status         FORMAT a9              HEADING 'Status' JUSTIFY right
COLUMN oracle_username        FORMAT a14             HEADING 'Oracle User' JUSTIFY right
COLUMN os_username            FORMAT a12             HEADING 'O/S User' JUSTIFY right
COLUMN os_pid                 FORMAT 9999999         HEADING 'O/S PID' JUSTIFY right
COLUMN session_program        FORMAT a18             HEADING 'Session Program' TRUNC
COLUMN session_machine        FORMAT a15             HEADING 'Machine' JUSTIFY right
COLUMN number_of_undo_records FORMAT 999,999,999,999 HEADING "# Undo Records"
COLUMN used_undo_size         FORMAT 999,999,999,999 HEADING "Used Undo Size"
SELECT
 s.sid sid
 , s.serial# serial_id
 , lpad(s.status,9) session_status
 , lpad(s.username,14) oracle_username
 , lpad(s.osuser,12) os_username
 , lpad(p.spid,7) os_pid
 , b.used_urec number_of_undo_records
 , b.used_ublk * d.value used_undo_size
 , s.program session_program
 , lpad(s.machine,15) session_machine
FROM
 v$process p
 , v$session s
 , v$transaction b
 , v$parameter d
WHERE
 b.ses_addr = s.saddr
 AND p.addr = s.paddr
 AND s.audsid <> userenv('SESSIONID')
 AND d.name = 'db_block_size';




uncommited transactions on rac 


set linesize 200 pagesize 300
column session_status         format a9              heading 'Status' JUSTIFY right
column oracle_username        format a14             heading 'Oracle User' JUSTIFY right
column os_username            format a12             heading 'O/S User' JUSTIFY right
column os_pid                 format 9999999         heading 'O/S PID' JUSTIFY right
column session_program        format a18             heading 'Session Program' TRUNC
column session_machine        format a15             heading 'Machine' JUSTIFY right
column number_of_undo_records format 999,999,999,999 heading "# Undo Records"
column used_undo_size         format 999,999,999,999 heading "Used Undo Size"
column kill                   format a15
SELECT distinct
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
 , lpad(s.status,9) session_status
 , lpad(s.username,14) oracle_username
 , lpad(s.osuser,12) os_username
 , lpad(p.spid,7) os_pid
 , b.used_urec number_of_undo_records
 , b.used_ublk * d.value used_undo_size
 , s.program session_program
 , lpad(s.machine,15) session_machine
 , s.sql_id
 , s.PREV_SQL_ID
FROM
   gv$process p
 , gv$session s
 , gv$transaction b
 , gv$parameter d
where 1=1
 and (b.ses_addr = s.saddr and b.inst_id= s.inst_id)
 and (p.addr = s.paddr and p.inst_id= s.inst_id)
 and s.audsid <> userenv('SESSIONID')
 and d.name = 'db_block_size';




























at



















2 comments:








  1. Anuj Singh16 April 2017 at 13:36
    https://anuj-singh.blogspot.co.uk/2017/04/oracle-open-transactions-info.html
    ReplyDelete
  2. Anuj Singh31 January 2019 at 12:08

    set linesize 300 pagesize 300 
    col kill for a15
    col sql_text for a50 wrap
    select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, t.used_urec records, t.used_ublk blocks,(t.used_ublk*8192/1024) kb,s.sql_id,sql.sql_text from gv$transaction t,gv$session s, gv$sql sql
    where 1=1
    and  t.addr=s.taddr
    and  t.inst_id=s.inst_id
    and s.sql_id = sql.sql_id
    and  s.inst_id=sql.inst_id;


    ReplyDelete