Rem Filename   : v_session.sql
Rem 
Rem Parameters : &1 = the SID of the session to be displayed.
Rem
Rem Author     : 
Rem
Rem Modification history
Rem --------------------
Rem 12-May-2005 V1.0  Initial Creation
Rem 16-May-2006 V1.1  Renamed v$session.sql --> v_session.sql because 
Rem                   the $-char is unhandy on Unix.
Rem
Rem ======================================================================================================
Rem
Rem Description : Displays the information in v$session in a nicely formatted way.
Rem               Some information is decoded, the native value is also shown in those cases.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<DBARETL>  @v_session 8
Rem 
Rem SESSION INFORMATION (selected from v$session)
Rem ------------------------------------------------------------------------------------------------------
Rem Session address         : 2C1944B8                      Module                 : SQL*Plus
Rem Session (sid,serial)    : 8,101                         Module hash            : 3669949024
Rem Audit sid               : 3596                          Action                 :
Rem Process address         : 2C180940                      Action hash            : 4029777240
Rem Username (#)            : MXXXXXXX (21)                 Client info            :
Rem Schemaname (#)          : MXXXXXXX (21)                 Fixed table sequence   : 1719
Rem Session logon time      : 2004-Feb-10 18:08:33          ----------------------------------------------
Rem Session status          : ACTIVE                        Trx state obj address  :
Rem Session type            : USER                          Lock addr waiting for  :
Rem Server type             : DEDICATED                     Object waiting for     : -1 (None)
Rem ------------------------------------------------------- File#, Block#, Row#    : 0, 0, 0
Rem Command                 : 3 (SELECT)                    ----------------------------------------------
Rem SQL address, hash       : 2F6BE668, 444648219           Session idle time      : 2 = 0:00:02
Rem Prev SQL address, hash  : 2F6BE668, 444648219           ----------------------------------------------
Rem ------------------------------------------------------- Paralled DML           : DISABLED
Rem Client OS username      : SZMS2K                        Paralled DDL           : ENABLED
Rem Client process id       : 628:1336                      Paralled query         : ENABLED
Rem Client terminal name    : W2SZMS2K02                    ----------------------------------------------
Rem Client machine name     : EDSRDBEAH\W2SZMS2K02 
Rem Client program          : sqlplusw.exe
Rem 
Rem ======================================================================================================


SET SERVEROUTPUT  ON
SET FEEDBACK   OFF
declare
  v_inst_name       varchar2(30);
  startup_time      date;
  v_host_name       varchar2(100);
  uptime            number;
  uptime_days       number;
  uptime_hours      number;
  uptime_minutes    number;

begin
  SELECT instance_name, startup_time, host_name
    INTO v_inst_name, startup_time, v_host_name
    FROM v$instance;

  uptime := sysdate - startup_time;
  uptime_days := trunc(uptime);
  uptime_hours := trunc((uptime - uptime_days)*24);
  uptime_minutes := trunc((uptime - ( uptime_days + uptime_hours/24 ))*60*24);

  if ( &1 > 0 ) then dbms_output.put_line( chr(10) || rpad( '-', 107, '-' ) ); end if;
  dbms_output.put_line( 'BBDD              : ' || v_inst_name || ' en el servidor ' || v_host_name );
  if ( instr( 'DC', 'S' ) > 0 ) then
    dbms_output.put_line( 'Startup date and time : ' || to_char( startup_time, 'DD-MON-YYYY HH24:MI:SS')
                                                     || '  ( uptime = ' || trunc(uptime) || ' days, ' 
                                                     || uptime_hours || ' hours and '
                                                     || uptime_minutes || ' minutes )');
  end if;
  if ( instr( 'DC', 'C' ) > 0 ) then
    dbms_output.put_line( 'Fecha actual      : ' || to_char( SYSDATE, 'DD-MON-YYYY HH24:MI:SS' ) );
  end if;
  if ( 107 > 0 ) then dbms_output.put_line( rpad( '-', 107, '-' ) ); end if;
end;
/

-------------------------------------------------------------------------------------------------------------------------------

SET FEEDBACK   OFF
DEFINE schema = ''
COLUMN schema_val NOPRINT NEW_VALUE schema
SELECT '&&1' schema_val FROM dual;

SET TERMOUT    OFF


DEFINE pga_mem = 0
DEFINE pga_max = 0
DEFINE uga_mem = 0
DEFINE uga_max = 0
DEFINE ServerName = '?'

COLUMN statistic# NOPRINT NEW_VALUE pga_mem
SELECT statistic# FROM v$statname WHERE name = 'session pga memory';

COLUMN statistic# NOPRINT NEW_VALUE pga_max
SELECT statistic# FROM v$statname WHERE name = 'session pga memory max';

COLUMN statistic# NOPRINT NEW_VALUE uga_mem
SELECT statistic# FROM v$statname WHERE name = 'session uga memory';

COLUMN statistic# NOPRINT NEW_VALUE uga_max
SELECT statistic# FROM v$statname WHERE name = 'session uga memory max';

COLUMN host_name NOPRINT NEW_VALUE ServerName
SELECT host_name FROM v$instance;


SET PAGESIZE   9999
SET TERMOUT    ON


CLEAR COLUMNS
COLUMN UserName     FORMAT A20             HEADING "User name"
COLUMN Sid,Serial   FORMAT a10            HEADING "Sid,Serial"
COLUMN Sid          FORMAT 9999            HEADING "Sid"
COLUMN Serial       FORMAT 99999999        HEADING "Serial#"
COLUMN service_name   FORMAT a20 HEADING "service_name"
COLUMN sql_id        FORMAT A15 HEADING "SqlId"
COLUMN SessPgaMem   FORMAT 9G999G999       HEADING "Dedic svr|Curr pga"
COLUMN SessPgaMax   FORMAT 9G999G999       HEADING "Dedic svr|Max pga"
COLUMN SessUgaMem   FORMAT 9G999G999       HEADING "Cli proc|Curr uga"
COLUMN SessUgaMax   FORMAT 9G999G999       HEADING "Cli proc|Max uga"
COLUMN WhereClient  FORMAT A1              HEADING "C"
COLUMN Status       FORMAT A1              HEADING "S"
COLUMN Call         FORMAT 9999            HEADING "Call"
COLUMN MemoryType   FORMAT A26             HEADING "Memory usage overview"
COLUMN MemoryInUse  FORMAT 9G999G999G999   HEADING "In use (KBytes)"


SELECT nvl(sess.username, ( SELECT 'bg: ' || description 
                              FROM v$bgprocess bg
                             WHERE bg.paddr = sess.paddr and rownum <= 1))  "UserName",
       sess.sid||','||sess.serial#                           "Sid,Serial",
       --sess.sid                                              "Sid",
       --sess.serial#                                          "Serial",
       sess.service_name                                   "service_name",
       sess.sql_id                                           "sql_id",
       sess_pga_mem.value/1024                               "SessPgaMem",
       sess_pga_max.value/1024                               "SessPgaMax",
       sess_uga_mem.value/1024                               "SessUgaMem",
       sess_uga_max.value/1024                               "SessUgaMax",
       decode( sess.machine, '&ServerName', 'L', 
                             'R' )                           "WhereClient",
       substr(sess.status,1,1) "Status",
       sess.last_call_et Call
  FROM v$session sess,
       ( SELECT sid, value FROM v$sesstat
          WHERE statistic# = &pga_mem ) sess_pga_mem,
       ( SELECT sid, value FROM v$sesstat
          WHERE statistic# = &pga_max ) sess_pga_max,
       ( SELECT sid, value FROM v$sesstat
          WHERE statistic# = &uga_mem ) sess_uga_mem,
       ( SELECT sid, value FROM v$sesstat
          WHERE statistic# = &uga_max ) sess_uga_max
 WHERE sess_pga_mem.sid = sess.sid
   AND sess_pga_max.sid = sess.sid
   AND sess_uga_mem.sid = sess.sid
   AND sess_uga_max.sid = sess.sid
   AND sess.type = 'USER'
   AND sess.sid = &schema
 ORDER BY sess.username;

-----------------------------------------------------------------------------------------------------------------------------------------------------


SET PAGESIZE  0
SET TRIMSPOOL ON
SET FEEDBACK  OFF
SET VERIFY    OFF
SET TERMOUT   OFF

DEFINE column_width = 30
DEFINE ActState = ''

COLUMN ActiveState NOPRINT NEW_VALUE ActState
SELECT CASE WHEN version < '9' 
              THEN '' 
              ELSE '''Active state           : '' || active_state || ' 
            END ActiveState
  FROM v$instance;

SET TERMOUT ON






PROMPT
SELECT 'SESSION INFORMATION (selected from v$session)
------------------------------------------------------------------------------------------------------
Session address         : ' || rpad(rawtohex(saddr),&column_width)  
                            || 'Module                 : ' || module || '
Session (sid,serial)    : ' || rpad(to_char(sid)||','||to_char(serial#),&column_width)          
                            || 'Module hash            : ' || to_char(module_hash) || '
Audit sid               : ' || rpad(to_char(audsid),&column_width)       
                            || 'Action                 : ' || v$session.action || '
Process address         : ' || rpad(rawtohex(paddr),&column_width)       
                            || 'Action hash            : ' || to_char(action_hash) || '
Username (#)            : ' || substr(rpad(username||' ('||to_char(user#)||')',&column_width),1,&column_width)
                            || 'Client info            : ' || client_info || '
Schemaname (#)          : ' || substr(rpad(schemaname||' ('||to_char(schema#)||')',&column_width),1,&column_width) 
                            || 'Fixed table sequence   : ' || to_char(fixed_table_sequence) || '
Session logon time      : ' || rpad(to_char(logon_time,'YYYY-Mon-DD HH24:MI:SS'),&column_width) || rpad('-',46,'-') || '
Session status          : ' || rpad(status,&column_width) || 'Trx state obj address  : ' || taddr || '
Event                   : '|| rpad(event,&column_width) || 'Lock addr waiting for  : ' || lockwait || '
Session type            : ' || rpad(type,&column_width) || 'Blocking session       : ' || blocking_instance||'-'||blocking_session|| '
Server type             : ' || rpad(server,&column_width) || 'Object waiting for     : ' 
                            || to_char(row_wait_obj#) || decode(row_wait_obj#, -1, ' (None)','') ||'
--------------------------' || lpad(' ',&column_width,'-') 
                            || 'File#, Block#, Row#    : ' || to_char(row_wait_file#) || ', '
                            || to_char(row_wait_block#) || ', ' || to_char(row_wait_row#) || '
Command                 : ' || rpad(to_char(command) || ' (' || audit_actions.name || ')',&column_width) || rpad('-',46,'-') ||'
last_call_et            : ' || to_char(last_call_et) || 's = ' 
                            || ltrim(to_char(floor(last_call_et/3600),'99')) || ':'
                            || ltrim(to_char(floor(mod(last_call_et,3600)/60),'09')) || ':'
                            || ltrim(to_char(mod(mod(last_call_et,3600),60),'09'))  ||'               Resource Consumer Group : ' || rpad(nvl(RESOURCE_CONSUMER_GROUP,' '),&column_width) ||'
SQL address, hash, child: ' || rpad(rawtohex(sql_address) || ', '||to_char(sql_hash_value) || ', '||to_char(SQL_CHILD_NUMBER),&column_width)  
                            || ' SQL_ID: '|| sql_id ||'
Prev SQL address, hash  : ' || rpad(rawtohex(prev_sql_addr) || ', '||to_char(prev_hash_value),&column_width) || 'PREV_SQL_ID: '|| prev_sql_id || '
--------------------------' || lpad(' ',&column_width,'-') || rpad('-',46,'-') || '
Client OS username      : ' || rpad(nvl(osuser,' '),&column_width) || 'Paralled DML           : ' || pdml_status || '
Client process id       : ' || rpad(nvl(process,' '),&column_width) || 'Paralled DDL           : ' || pddl_status || ' 
Client terminal name    : ' || rpad(nvl(terminal,' '),&column_width) || 'Paralled query         : ' || pq_status || '
Client machine name     : ' || rpad(nvl(machine,' '),&column_width) || rpad('-',46,'-') || '
Client program          : ' || program || ' - ' || module || '
Service Name            : ' || service_name
 FROM v$session left outer join audit_actions on v$session.command = audit_actions.action
WHERE sid = &1;

UNDEFINE column_width
UNDEFINE ActiveState

SET FEEDBACK ON
SET PAGESIZE 9999