Personal tools
You are here: Home Oracle Problem Resolution Script to check the database transactions
Log in

Forgot your password?
Document Actions

Script to check the database transactions

by Carmine Andre Marrone last modified 2008-04-15 18:47

Prompt +----------------------------------------------------+
Prompt | User Transactions Information                      |
Prompt +----------------------------------------------------+

column "UserName" format a8
column "DB Sid" format 999999
column "Unix Pid" format 99999999
column "Trnx_start_time" format a19
column "Current Time" format a19
column "Elapsed(mins)" format 999999999.99
column "Undo Name" format a09
column "Used Undo Blks" format a13
column "Used Undo Size(Kb)" format a17
column "Logical I/O(Blks)" format 99999999999999999
column "Logical I/O(Kb)" format 999999999999999
column "Physical I/O(Blks)" format 999999999999999999
column "Physical I/O(Kb)" format 999999999999999999

    a.username  "UserName"
  , a.sid       "DB Sid"
  , e.spid      "Unix Pid"
  , TO_CHAR(TO_DATE(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time"
  , TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time"
  , ROUND(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)"
  , c.segment_name "Undo Name"
  , TO_CHAR(b.used_ublk*d.value/1024) "Used Undo Size(Kb)"
  , TO_CHAR(b.used_ublk) "Used Undo Blks"
  , b.log_io "Logical I/O(Blks)"
  , b.log_io*d.value/1024 "Logical I/O(Kb)"
  , b.phy_io "Physical I/O(Blks)"
  , b.phy_io*d.value/1024 "Physical I/O(Kb)"
  , a.program
    v$session         a
  , v$transaction     b
  , dba_rollback_segs c
  , v$parameter       d
  , v$process         e
      b.ses_addr = a.saddr
  AND b.xidusn   = c.segment_id
  AND     = 'db_block_size'
  AND e.ADDR     = a.PADDR

Security Awareness
Would you like your company to implement gamification into your security awareness program?