@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display current session's details
*  Parameters : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  11-Feb-15  Vishal Gupta  Shortened columns output width
*  20-Apr-12  Vishal Gupta  Created
*
*/

COLUMN SID                                                        FORMAT 99999
COLUMN PID                                                        FORMAT 999999
COLUMN SPID                                                       FORMAT a6
COLUMN Process                                                    FORMAT a11
COLUMN instance_number        HEADING "I#"                        FORMAT 99
COLUMN serial#                HEADING "Serial#"                   FORMAT 99999
COLUMN database_role          HEADING "Database|Role"             FORMAT a9
COLUMN db_unique_name         HEADING "DB Unique|Name"            FORMAT a20
COLUMN instance_name          HEADING "Instance|Name"             FORMAT a10
COLUMN host_name              HEADING "DB HostName"               FORMAT a15  TRUNCATE
COLUMN machine                HEADING "Client|Machine"            FORMAT a20  TRUNCATE
COLUMN program                HEADING "Client|Program"            FORMAT a15  TRUNCATE
COLUMN process                HEADING "Client|Process"            FORMAT a15  

SELECT s.sid
     , i.instance_number
     , s.serial#
     , p.pid
     , p.spid
     , d.database_role
     , d.db_unique_name
     , i.instance_name
     , i.host_name
     , s.machine
     , s.program
     , s.process
FROM v$instance i
     JOIN v$database d ON 1=1
     JOIN (select sid from v$mystat where rownum = 1) m ON 1=1
     JOIN v$session s ON s.sid = m.sid
     JOIN v$process p ON p.addr = s.paddr
WHERE 1=1
;

@@footer