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.

Saturday, December 11, 2010

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;

No comments: