Important DBA Scripts

Hi Friends,

Many people as requested for some sql query which helpful in oracle. Please find the below SQL Query which are very important for any DBA. Hope these may useful and helpful. If need any more sql scripts, please let us know.

Script to get the session details of the sessions holding/waiting on a Library Cache Lock: -- very very important

select /*+ ordered */

           w.kgllktype "LockType",

           w.kgllkhdl  "ObjectHandle",

           q.sql_text  "ObjectName",

           h1.sid      "HoldingSID",

 decode(h.kgllkmod,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown') "Held",

 w1.sid       "WaitingSID",

 decode(w.kgllkreq,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown') "Request"

 from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1, v$sql q

 where

      (((h.kgllkmod != 0) and (h.kgllkmod != 1)

         and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

       and

         (((w.kgllkmod = 0) or (w.kgllkmod= 1))

         and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

      and w.kgllkhdl  = q.address(+)

      and w.kgllktype = h.kgllktype

      and w.kgllkhdl  = h.kgllkhdl

      and w.kgllkuse  = w1.saddr

      and h.kgllkuse  = h1.saddr

/

How to check Request ID status scripts:

select A.REQUEST_ID,TO_CHAR(A.ACTUAL_START_DATE,'DD-MON-YY HH24:MI:SS'),TO_CHAR(A.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24:MI:SS'),A.STATUS_CODE,A.PHASE_CODE,

B.CONCURRENT_PROGRAM_ID , B.CONCURRENT_PROGRAM_NAME 

from fnd_concurrent_requests A, fnd_concurrent_programs B

where request_id=&reqid AND

A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID ;

Scripts for Running Request ID details:

# This query returns all running concurrent manager requests

# there are no parameters required for running this query.

select oracle_process_id,request_id 

from applsys.fnd_concurrent_requests

where PHASE_CODE='R';

Find the details of the Request ID:

SELECT v.sid,v.serial#,s.sql_text,f.request_id

FROM v$session v,v$sqlarea s,v$process p,fnd_concurrent_requests f

WHERE f.request_id='&Request_ID'

AND ((v.process=f.os_process_id) OR (p.spid=f.os_process_id))

AND v.paddr=p.addr

AND v.sql_hash_value=s.hash_value;

OR

How to find Request ID to SID or request id details:

SELECT fr.request_id "REQ_ID", s.sid, s.serial#, s.lockwait, s.module, s.status, p.spid "S_PID", s.process "C_PID"

FROM

v$session s, v$process p, applsys.fnd_concurrent_requests fr, applsys.fnd_concurrent_processes fp

WHERE

s.paddr = p.addr

AND s.process = fp.os_process_id

AND fr.controlling_manager = fp.concurrent_process_id

AND fr.request_id = '&reqid';

How to find out SID from Request ID:

SELECT 'Request id: '||request_id ,

'SID Serial: '||ses.sid||','|| ses.serial#,

'Module : '||ses.module

FROM apps.fnd_concurrent_requests req, v$session ses, v$process proc,

v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,

apps.fnd_executables execname 

WHERE

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 and prog.executable_id=execname.executable_id

and request_id=’1343130’;

Script for Forms query:

# This query requires no parameters and will show you

# all forms that people in applications are using.

# It is similar to the user monitor screen in applications

#

#

#

set pause on

set pause '--more--'

column user_name format a20

column user_form_name format a25

column time format a8

column pid format 99999

column spid format 99999

column process format 99999

SELECT v$process.pid,spid,user_name,time,user_form_name

FROM fnd_signon_audit_view, v$process

WHERE fnd_signon_audit_view.pid = v$process.pid

/

Some more useful links:

Blocking Session

Deadlock Concepts and Solution

Crontab Concepts and Details/Examples

R12.1 Vs R12.2

Interviews Questions

Jobs Tips

For any query or suggestions, please write either in comment box or contact us @https://ora-data.blogspot.in/

Thanks,