R12 DBA Queries

R12 DBA Queries

SELECT oracle_process_id FROM fnd_concurrent_requests WHERE request_id=<<conc request id>>

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    where 1=1
    AND req.request_id = :Request ID
    AND req.oracle_process_id=proc.spid(+)
    AND proc.addr = ses.paddr(+)
    AND dest.NAME='user_dump_dest'
    AND dbnm.NAME='db_name'
    AND req.concurrent_program_id = prog.concurrent_program_id
    and REQ.PROGRAM_APPLICATION_ID = PROG.APPLICATION_ID
    AND prog.application_id = execname.application_id

SELECT * FROM V$PARAMETER WHERE name LIKE '%dump%'


ALTER SYSTEM KILL SESSION 'sid,serial#'

   SELECT a.object_id,
  a.session_id,
  SUBSTR(b.object_name, 1, 40)
FROM v$locked_object a,
  dba_objects b
WHERE A.object_id = b.object_id
AND b.object_name LIKE 'AP_%'
ORDER BY b.object_name;


SELECT l.*,
  o.owner object_owner,
  o.object_name
FROM SYS.all_objects o,
  v$lock l
WHERE l.TYPE       = 'TM'
AND o.object_id    = l.id1
AND o.object_name IN ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');

  select s1.username || '-' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '-' || s2.machine || ' ( SID=' || s2.sid || ' )' AS blocking_status
  from v$lock l1,
       v$session s1,
       v$lock l2,
       v$session s2
 where s1.sid=l1.sid
   and s2.sid=l2.sid
   and l1.BLOCK = 1
   and l2.request > 0
   AND l1.id1 = l2.id1
   and l2.id2 = l2.id2;


SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, dba_Objects B
WHERE A.Object_ID = B.Object_ID;



select * from v$database