[10g] 현재의 USER, SESSION, PROCESS 정보 출력 뷰

/********************************************************************************************
-- Title : [10g] 현재의 USER, SESSION, PROCESS 정보 출력 뷰
-- Key word :
********************************************************************************************/

create or replace view sys.sp_who0
as
    select u.sid, u.serial#, u.status || '(' || u.type || ')' "STATUS", u.username, u.schemaname
         , replace(u.terminal, 'UNKNOWN', '  .') "TERMINAL"
         , NVL(cast(u.blocking_session as varchar2(10)), '  .') "BLKBY", u.event, u.program, u.module
         , u.version_count, u.executions, u.cpu_time, u.elapsed_time, u.last_active_time, u.logon_time
         , NVL(u.active_sql_text, u.inactive_sql_text) "SQL_TEXT"
    from
    (
        select a.*, c.version_count, c.executions, c.last_active_time, c.cpu_time, c.elapsed_time
             , (select sql_text from v$sql
                  where hash_value = a.sql_hash_value and address = a.sql_address and parsing_schema_name = a.username
                 ) as active_sql_text
             , (select sql_text from v$sql
                  where address = a.prev_sql_addr and parsing_schema_name = a.username
                 ) as inactive_sql_text
        from v$session a
        inner join v$process b
        on a.paddr = b.addr
        left outer join v$sqlarea c
        on a.sql_id = c.sql_id
    ) u
    order by u.sid;
-- select * from sys.sp_who0;

grant select on sp_who0 to public;