rem *************************************************************

rem * Name : SESSION.SQL

rem *

rem * Date: July 15 2001

rem * Purpose: Show session information

rem *

rem * Downloaded from www.oradev.com

rem *************************************************************



set lines 120

column sid format a5

column client format a13

column inlogtijd heading logon_time

column inlogtijd format a18

column ora_user heading oracle_user

column ora_user format a12

column serial#  format a5

column serial#  heading ser#

column programma format a35

column computer   format a17

select DECODE( S.TYPE,'BACKGROUND','INTERNAL'

               , username ) ora_user

,      SUBSTR(DECODE(UPPER(SUBSTR(S.TERMINAL,1,3)),'PTS',

                     MACHINE||':'||S.OSUSER,

                         DECODE(SUBSTR(S.TERMINAL,1,1),'?',

                                S.MACHINE||':'||S.OSUSER,

                                    DECODE(S.TERMINAL,null,

                                           S.MACHINE||

                                           DECODE(S.OSUSER,null,'',':')||S.OSUSER,

              DECODE(S.TERMINAL,'DOS PC',S.TERMINAL,

              SUBSTR(S.MACHINE,INSTR(S.MACHINE,'\',1)+1,6)||':'||S.OSUSER))))

                 ,1,13) CLIENT

, decode( SUBSTR(S.MACHINE,INSTR(S.MACHINE,'\',1)+1,6)

         , 'W12021' , 'Edwin Korsten'

         , s.machine)                           Computer

  ,      to_char(sid) sid

  ,      to_char(serial#) serial#

 , to_char(logon_time,'DD-MM-YYYY HH24:MI')   logon_time

 , substr(DECODE(LOCKWAIT,NULL,

   DECODE(COMMAND, 0,'NO OP',

                         1,'CR TAB', 2,'INSERT', 3,'SELECT', 4,'CR CLU',

                                5,'AL CLU', 6,'UPDATE', 7,'DELETE', 8,'DROP',

                                9,'CR IND',10,'DR IND',11,'AL IND',12,'DR TAB',

                                13,'CR SEQ',14,'AL SEQ',15,'AL TAB',16,'DR SEQ',

                               17,'GRANT' ,18,'REVOKE',19,'CR SYN',20,'DR SYN',

                               21,'CR VIW',22,'DR VIW',23,'VAL IND',24,'CR PRC',

                               25,'AL PRC',26,'LK TAB',27,'NO-OPE',28,'RENAME',

                               29,'COMMEN',30,'AUDIT' ,31,'NOAUD',32,'CR DBLNK',

                               33,'DR DBLNK',34,'CR DB',35,'AL DB',36,'CR RBS',

                               37,'AL RBS',38,'DR RBS',39,'CR TSP',40,'AL TSP',

                               41,'DR TSP',42,'AL SES',43,'AL USR',44,'COMMIT',

                               45,'ROLLBCK',46,'SAVEPNT',47,'EX PLS',

                               48,'SET TRN',49,'SW LOG',50,'EXPLAIN',

                               51,'CR USR',52,'CR ROL',53,'DR USR',54,'DR ROL',

                               55,'SET ROL',56,'CR SCH',57,'CR CFIL',

                               58,'AL TRC',59,'CR TRG',60,'AL TRG',61,'DR TRG',

                               62,'AN TAB',63,'AN IND',64,'AN CLU',65,'CR PROF',

                               67,'DR PROF',68,'AL PROF',69,'DR PRC',70,'AL RC',

                               71,'CR SNLOG',72,'AL SNLOG',73,'DR SNLOG',

                               74,'CR SNSHT',75,'AL SNSHT',76,'DR SNSHT',

                               79,'AL ROL',85,'TRN TAB',86,'TRN CLU',

                               88,'AL VW',91,'CR FNC',92,'AL FNC',93,'DR FNC',

                               94,'CR PCK',95,'AL PCK',96,'DR PCK',

                               97,'CR PCKB',98,'AL PCKB',99,'DR PCKB'

                     ,to_char(command)),'WAITING')

             ,1,10) COMMAND

 from v$session s

 order by username

 ;