/*
* 
*  Author   : Vishal Gupta
*  Purpose  : Displays long running transactions
*  Parameter: 1 - Where Clause
* 
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  04-May-15  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/

UNDEFINE WHERE_CLAUSE
DEFINE WHERE_CLAUSE="&&1"


/************************************
*  CONFIGURATION PARAMETERS
************************************/

DEFINE BYTES_FORMAT="999,999"
--DEFINE BYTES_DIVIDER="1024"
--DEFINE BYTES_HEADING="KB"
DEFINE BYTES_DIVIDER="1024/1024"
DEFINE BYTES_HEADING="MB"
--DEFINE BYTES_DIVIDER="1024/1024/1024"
--DEFINE BYTES_HEADING="GB"


DEFINE COUNT_FORMAT=999,999
--DEFINE COUNT_DIVIDER="1"
--DEFINE COUNT_HEADING="#"
DEFINE COUNT_DIVIDER="1000"
DEFINE COUNT_HEADING="#1000"


PROMPT
PROMPT *********************************************
PROMPT * D A T A B A S E   T R A N S A C T I O N S
PROMPT *********************************************

COLUMN xid                      HEADING "XID"                      FORMAT a16 
COLUMN sid                      HEADING "SID"                      FORMAT 99999
COLUMN inst_id                  HEADING "I#"                       FORMAT 99 
COLUMN spid                     HEADING "SPID"                     FORMAT a6
COLUMN transaction_start_date   HEADING "Transaction|StartTime"    FORMAT a18 
COLUMN tran_duration            HEADING "Transaction|Duration"     FORMAT a15 
COLUMN transaction_status       HEADING "Tran|Status"              FORMAT a8 
COLUMN space                    HEADING "Space|Tran"               FORMAT a5 
COLUMN recursive                HEADING "Recu|rsive|Tran"          FORMAT a5 
COLUMN noundo                   HEADING "No|Undo|Tran"             FORMAT a4 
COLUMN ptx                      HEADING "Par'l|Tran"               FORMAT a5 
COLUMN used_undo                HEADING "Undo|(&&BYTES_HEADING)"   FORMAT &&BYTES_FORMAT
COLUMN username                 HEADING "UserName"                 FORMAT a20
COLUMN osuser                   HEADING "OS User"                  FORMAT a15 TRUNCATED
COLUMN status                   HEADING "Session|Status"           FORMAT a8
COLUMN state                    HEADING "Session|State"            FORMAT a12 TRUNCATED
COLUMN logon_time               HEADING "Logon Time"               FORMAT a18
COLUMN MACHINE                  HEADING "Machine"                  FORMAT a20 TRUNCATED
COLUMN process                  HEADING "Process"                  FORMAT a11 
COLUMN program                  HEADING "Program"                  FORMAT a20 TRUNCATED
COLUMN event                    HEADING "Event"                    FORMAT a30 TRUNCATED
--COLUMN last_call_et           HEADING "LastCall|(sec)"             FORMAT 999,999        
COLUMN last_call_et             HEADING "LastCall"                 FORMAT a12
COLUMN sql_child_number         HEADING "SQL|Child|No"             FORMAT 99

COLUMN log_io                   HEADING "Logical|IO|(&&COUNT_HEADING)"       FORMAT &&COUNT_FORMAT
COLUMN phy_io                   HEADING "Physical|IO|(&&COUNT_HEADING)"      FORMAT &&COUNT_FORMAT
COLUMN cr_get                   HEADING "Consistent|Gets|(&&COUNT_HEADING)"  FORMAT &&COUNT_FORMAT

COLUMN locked_mode              HEADING "Lock Mode"                FORMAT a10
COLUMN object_name              HEADING "ObjectName"               FORMAT a35


SELECT 
     --  t.xid   xid
     --, t.ptx_xid
      s.sid sid
     , t.inst_id
     , p.spid
     , s.status
     , TO_CHAR(t.start_date,'DD-MON-YY HH24:MI:SS')  transaction_start_date
     , FLOOR(sysdate - t.start_date) || 'd '
       || LPAD(FLOOR(MOD((sysdate - t.start_date) , 1) * 24 ) ,2) || 'h '
       || LPAD(FLOOR(MOD((sysdate - t.start_date) * 24 , 1) * 60 ) ,2) || 'm '
       || LPAD(FLOOR(MOD((sysdate - t.start_date) * 24 * 60 , 1) * 60 ) ,2) || 's ' tran_duration
     , t.status      transaction_status
     , t.space
     , t.recursive
     , t.noundo
     , t.ptx
     , DECODE(lo.locked_mode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
            TO_CHAR(lo.locked_mode)
            )  locked_mode
     , NVL2(o.owner,o.owner|| '.' || o.object_name || NVL2(o.subobject_name,'(' || o.subobject_name || ')','') , '' ) object_name
     , ROUND((t.used_ublk * p.value)/&&BYTES_DIVIDER) used_undo
     , t.log_io/&&COUNT_DIVIDER log_io
     , t.phy_io/&&COUNT_DIVIDER phy_io
     , t.cr_get/&&COUNT_DIVIDER cr_get
     , s.username
     , s.osuser
--     , s.sql_id
--     , s.sql_child_number
     , s.program
FROM gv$transaction t
     INNER JOIN gv$session s ON t.inst_id = s.inst_id   AND t.ses_addr = s.saddr 
     INNER JOIN gv$process p ON p.inst_id = s.inst_id   AND p.addr = s.paddr
     INNER JOIN v$parameter p ON p.name = 'db_block_size'
     LEFT OUTER JOIN gv$locked_object lo ON t.inst_id = lo.inst_id 
                                        AND s.sid     = lo.session_id 
                                        AND t.xidusn  = lo.xidusn 
                                        AND t.xidslot = lo.xidslot 
                                        AND t.xidsqn  = lo.xidsqn 
     LEFT OUTER JOIN dba_objects o ON lo.object_id = o.object_id
WHERE 1=1 and 2=2   
-- had to put AND clause other when no where clause is passed it was giving following error
-- SP2-0341: line overflow during variable substitution (>3000 characters at line 53)
 &&WHERE_CLAUSE
ORDER BY transaction_start_date asc
;