Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, March 15, 2011

Active Process in DB

Create Or Replace Force View Sys.Av_db_active_process (Username,
                                                       Osuser,
                                                       Disk_reads,
                                                       Buffer_gets,
                                                       Lockwait,
                                                       Pid,
                                                       Event,
                                                       Sql
                                                      )

As
   Select   Substr (V$session.Username, 1, 8) Username,
            V$session.Osuser Osuser,
--        DECODE(V$SESSION.SERVER,'DEDICATED','D','SHARED','S','O') SERVER,
                                    V$sqlarea.Disk_reads Disk_reads,
            V$sqlarea.Buffer_gets Buffer_gets,
            Substr (V$session.Lockwait, 1, 10) Lockwait,
            V$session.Process Pid, V$session_wait.Event Event,
            V$sqlarea.Sql_text Sql
       From V$session_wait, V$sqlarea, V$session
      Where V$session.Sql_address = V$sqlarea.Address
        And V$session.Sql_hash_value = V$sqlarea.Hash_value
        And V$session.Sid = V$session_wait.Sid(+)
        And V$session.Status = 'ACTIVE'
        And V$session_wait.Event != 'client message'
   Order By V$session.Lockwait Asc, V$session.Username;

No comments: