DBA Queries in Oracle Apps

Current Query Running for Request Id

SELECT C.sql_text,

       B.SID,

       B.SQL_ID,

       C.SQL_FULLTEXT,

       C.module

  FROM APPS.fnd_concurrent_requests A, V$SESSION B, V$SQLAREA C

 WHERE     A.oracle_session_id = B.audsid

       AND B.sql_hash_value = C.hash_value

       AND A.request_id =  <p_request_id>;

To Get More Details use the below query

  SELECT SQLT.hash_value,

         SQLT.sql_text,

         VSES.username,

         VSES.module,

         VSES.command

    FROM v$sqltext SQLT, v$session VSES, APPS.fnd_concurrent_requests FCONC

   WHERE     SQLT.hash_value = VSES.sql_hash_value

         AND FCONC.oracle_session_id = VSES.audsid

         AND FCONC.request_id =  < p_request_id  >

ORDER BY SQLT.piece

Query to Fetch DB Locked Objects

SELECT c.owner,

       c.object_name,

       c.object_type,

       b.sid,

       b.serial#,

       b.status,

       b.osuser,

       b.machine,

       b.*

  FROM v$locked_object a, v$session b, dba_objects c

 WHERE b.sid = a.session_id AND a.object_id = c.object_id;

Query to Sell all the Locked Objects

SELECT *from v$access WHERE object like 'XX%'

To Kill the Current Session

SELECT inst_id,sid,serial# FROM gv$session WHERE username='SCOTT';

ALTER SYSTEM KILL SESSION '130,620,@1';