Locked Object Queries

CREATE OR REPLACE VIEW locked_tables_v

/******************************************************************************

*     Query to identify the sessions which have locked a particular table     *

******************************************************************************/

AS

SELECT /*+ ORDERED */

       s.sid

      ,s.serial#

      ,do.object_name

      ,do.object_type

      ,do.owner

      ,s.status

      ,s.osuser

      ,s.machine

  FROM gv$session          s

      ,gv$locked_object    lo

      ,dba_objects         do

 WHERE s.sid               = lo.session_id

   AND lo.object_id        = do.object_id ;

CREATE OR REPLACE VIEW locked_objects_v

/******************************************************************************

*  Query to identify the sessions which have locked a particular Procedure,   *

*  Package or Concurrent Program.                                             *

******************************************************************************/

AS

SELECT s.sid

      ,s.serial#

      ,ddl.name

      ,ddl.type

      ,ddl.owner

      ,s.status

      ,s.osuser

      ,s.machine

  FROM dba_ddl_locks      ddl

      ,gv$session         s

 WHERE ddl.session_id     = s.sid ;

CREATE OR REPLACE VIEW locked_programs_v

/******************************************************************************

*  Query to identify the sessions which have locked a particular Concurrent   *

*  Program for known Request ID.                                              *

******************************************************************************/

AS

SELECT /*+ ORDERED */

       s.sid

      ,s.serial#

      ,s.status

      ,s.osuser

      ,s.machine

      ,fcr.request_id

      ,fcr.oracle_process_id

      ,fcr.status_code

      ,fcr.phase_code

      ,p.spid

  FROM fnd_concurrent_requests    fcr

      ,fnd_concurrent_processes   fcp

      ,gv$process                    p

      ,gv$session                    s

 WHERE fcr.controlling_manager       = fcp.concurrent_process_id

   AND fcp.oracle_process_id         = p.pid

   AND p.addr                        = s.paddr ;

  CREATE OR REPLACE VIEW sql_text_v

/******************************************************************************

*  Query to find out the DML a independent procedure or Concurrent Program is *

*  performing for known sid.                                                  *

******************************************************************************/

  AS

  SELECT s.sid

        ,s.serial#

        ,st.piece

        ,st.sql_text

        ,s.status

        ,s.osuser

        ,s.machine

    FROM gv$session        s

        ,gv$sqltext        st

   WHERE s.sql_hash_value  = st.hash_value(+)

ORDER BY st.piece ;