共有サーバ　セッション情報を表示する

set heading off;
pro+--------+-----+-------+---------------------------+----------+----+----+----------+-----------------+---------+----------+-------------+;
pro|status  |  sid|serial#|program                    |command   |sha |disp|lastcallet|logon_time       |server   |username  |osuser       |;
pro+--------+-----+-------+---------------------------+----------+----+----+----------+-----------------+---------+----------+-------------+;
select
       '|'||rpad(coalesce(a.status,' '),8)||
       '|'||lpad(coalesce(to_char(a.sid),' '),5)||
       '|'||lpad(coalesce(to_char(a.serial#),' '),7)||
       '|'||rpad(coalesce(a.program,' '),27)||
--     '|'||rpad(coalesce(e.module,' '),27)||
       '|'||rpad(coalesce(
                          decode(
                                 a.command,
                                     0,'NONE',
                                     1,'CREATE TABLE',
                                     2,'INSERT',
                                     3,'SELECT',
                                     6,'UPDATE',
                                     7,'DELETE',
                                     9,'CREATE INDEX',
                                     10,'DROP INDEX',
                                     12,'DROP TABLE',
                                     44,'COMMIT',
                                     45,'ROLLBACK',
                                     46,'SAVEPOINT',
                                     47,'PL/SQL EXECUTE',
                                     85,'TRUNCATE TABLE',
                                     'OTHER'
                                 )
                          ,' '),10)||
       '|'||rpad(coalesce(c.name,' '),4)||
       '|'||rpad(coalesce(b.name,' '),4)||
       '|'||lpad(coalesce(to_char(a.last_call_et),' '),10)||
       '|'||rpad(coalesce(to_char(a.logon_time,'YYYYMMDD-HH24:MI:SS'),' '),17)||
       '|'||rpad(coalesce(a.server,' '),9)||
       '|'||rpad(coalesce(a.username,' '),10)||
       '|'||rpad(coalesce(a.osuser,' '),13)||
       '|'
from v$session a
     full outer join v$dispatcher b on a.paddr = b.paddr
     full outer join (select a.name,b.dispatcher from v$shared_server a,v$circuit b where a.circuit = b.circuit) c on c.dispatcher = b.paddr
--     full outer join v$sqlarea e on a.sql_address = e.address
where a.sid is not null
      and a.username != 'SYS'
order by a.sid;
pro+--------+-----+-------+---------------------------+----------+----+----+----------+-----------------+---------+----------+-------------+;
pro|status  |  sid|serial#|program                    |command   |sha |disp|lastcallet|logon_time       |server   |username  |osuser       |;
pro+--------+-----+-------+---------------------------+----------+----+----+----------+-----------------+---------+----------+-------------+;
exit;