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

Locked Object

Create Or Replace Force View Sys.Av_locked_obj (Sid,
                                                Oracle_username,
                                                Os_user_name,
                                                Locked_mode,
                                                Object_name,

                                                Object_type,
                                                Owner,
                                                Last_ddl_time,
                                                Timestamp,
                                                Sess_status,
                                                Terminal,
                                                Program,
                                                Last_call_et,
                                                Blocking_session_status,
                                                Blocking_instance,
                                                Blocking_session,
                                                Event,
                                                Wait_class,
                                                State,
                                                Killstmt
                                               )
As
   Select A.Session_id Sid, A.Oracle_username, A.Os_user_name, A.Locked_mode,
          B.Object_name, B.Object_type, B.Owner, B.Last_ddl_time, B.Timestamp,
          C.Status Sess_status, C.Terminal, C.Program, C.Last_call_et,
          C.Blocking_session_status, C.Blocking_instance, C.Blocking_session,
          C.Event, C.Wait_class, C.State,
             'ALTER SYSTEM DISCONNECT SESSION '
          || ''''
          || C.Sid
          || ','
          || C.Serial#
          || ''' '
          || 'IMMEDIATE;' Killstmt
     From V$locked_object A, Dba_objects B, V$session C
    Where A.Object_id = B.Object_id
      And C.Sid = A.Session_id;

No comments: