DB Monitorng Views - Part 1 (Heterogeneous Services)

View Name: AV_HS_USERS_WAIT

Purpose: Represents the necessary info for all users who are waiting for the heterogeneous service response. Provides the statement to kill the associated session if any one is hanging with undue time.

Create Or Replace Force View Sys.Av_hs_users_wait (Logon_time,
                                                   Sid,
                                                   Serial#,
                                                   Status,
                                                   Last_call_et,
                                                   Username,
                                                   Osuser,
                                                   Terminal,
                                                   Program,
                                                   Command,
                                                   Lockwait,
                                                   Blocking_session_status,
                                                   Blocking_session,
                                                   Event,
                                                   State,
                                                   Service_name,
                                                   Killstmt
                                                  )
As
   Select   Logon_time, Sid, Serial#, Status, Last_call_et, Username, Osuser,
            Terminal, Program, Command, Lockwait, Blocking_session_status,
            Blocking_session, Event, State, Service_name,
               'Alter SYSTEM DISCONNECT Session '
            || ''''
            || Sid
            || ','
            || Serial#
            || ''' '
            || 'IMMEDIATE;' Killstmt
       From V$session
      Where (Program Not Like 'ORACLE.EXE%')
        And Program Not Like 'racgimon%'
        And Event Like 'HS%'
   Order By Last_call_et Desc

View Name: AV_HS_MY_ACTIVE_SESSION;

Purpose: Shows info about the dblinks active for the currently connected users.

Create Or Replace Force View Sys.Av_hs_my_active_session (Db_link,
                                                          Owner_id,
                                                          Logged_on,
                                                          Heterogeneous,
                                                          Protocol,
                                                          Open_cursors,
                                                          In_transaction,
                                                          Update_sent,
                                                          Commit_point_strength
                                                         )
As
   Select "DB_LINK", "OWNER_ID", "LOGGED_ON", "HETEROGENEOUS", "PROTOCOL",
          "OPEN_CURSORS", "IN_TRANSACTION", "UPDATE_SENT",
          "COMMIT_POINT_STRENGTH"
     From V$dblink;

View Name: AV_HS_HOW_LONG_OPEN

Purpose: Shows info about all session which have connection with heterogeneous server, how long they are open , their status etc.

Create Or Replace Force View Sys.Av_hs_how_long_open (Addr,
                                                      Sid,
                                                      Username,
                                                      Machine,
                                                      Last_call_et,
                                                      Status,
                                                      Hours_active,
                                                      Killstmt
                                                     )
As
   Select   T.Addr, S.Sid, S.Username, S.Machine, S.Last_call_et, S.Status,
              (Sysdate - To_date (T.Start_time, 'MM/DD/YY HH24:MI:SS')
              )
            * 24 As Hours_active,
               'ALTER SYSTEM DISCONNECT SESSION '
            || ''''
            || S.Sid
            || ','
            || S.Serial#
            || ''' '
            || 'IMMEDIATE;' Killstmt
       From V$transaction T, V$session S
      Where T.Addr = S.Taddr
   Order By Hours_active Desc;