Queries Arsenel for Oracle Apps DBA - Part 2

Queries Arsenel for Oracle Apps DBA - Part 2

**********************************************************

 INVALID OBJECTS

**********************************************************

How to check for invalid objects in a particular module?

select OWNER, OBJECT_NAME, OBJECT_TYPE

from DBA_OBJECTS

where OBJECT_NAME like ‘FND_%’

and STATUS = ‘INVALID’;

select OWNER, OBJECT_NAME, OBJECT_TYPE

from DBA_OBJECTS

where OBJECT_NAME like ‘AP_%’

and STATUS = ‘INVALID’;


How to check for invalid objects in all the modules?

select owner, object_name, object_type from dba_objects

where status = ‘INVALID’

order by object_name, object_type;



**********************************************************

CONCURRENT REQUEST

**********************************************************

-- To find out scheduled concurrent request



SELECT cr.request_id,

DECODE (cp.user_concurrent_program_name,

'Report Set', 'Report Set:'

cr.description,

cp.user_concurrent_program_name

) NAME,

argument_text, cr.resubmit_interval,

NVL2 (cr.resubmit_interval,

'PERIODICALLY',

NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',

'ONCE')

) schedule_type,

DECODE (NVL2 (cr.resubmit_interval,

'PERIODICALLY',

NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',

'ONCE')

),

'PERIODICALLY', 'EVERY '



cr.resubmit_interval



' '



cr.resubmit_interval_unit_code



' FROM '



cr.resubmit_interval_type_code



' OF PREV RUN',

'ONCE', 'AT :'



TO_CHAR (cr.requested_start_date, 'DD-MON-RR

HH24:MI'),

'EVERY: '

fcr.class_info

) schedule,

fu.user_name, requested_start_date

FROM apps.fnd_concurrent_programs_tl cp,

apps.fnd_concurrent_requests cr,

apps.fnd_user fu,

apps.fnd_conc_release_classes fcr

WHERE cp.application_id =

cr.program_application_id

AND cp.concurrent_program_id =

cr.concurrent_program_id

AND cr.requested_by = fu.user_id

AND cr.phase_code = 'P'

AND cr.requested_start_date > SYSDATE

AND cp.LANGUAGE = 'US'

AND fcr.release_class_id(+) = cr.release_class_id

AND fcr.application_id(+) =

cr.release_class_app_id;



-- Currently running concurrent request:-

select

sess.sid,sess.serial#,

oracle_process_id OS_PROCESS_ID,

fusr.description user_name ,

fcp.user_concurrent_program_name progName,

to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,

request_id RequestId,

TRUNC(((sysdate-fcr.actual_start_date)/(1/24))*60) exec_time

from

fnd_concurrent_requests fcr,

fnd_concurrent_programs_tl fcp,

fnd_user fusr,

v$session sess

where

fcp.concurrent_program_id = fcr.concurrent_program_id

and fcr.program_application_id = fcp.application_id

and fcp.language = 'US'

--and fcr.phase_code = 'R'

and fcr.status_code = 'R'

and fcr.requested_by = fusr.user_id

and fcr.oracle_session_id = sess.audsid (+)

order by 5 DESC



How to find database SID from a Concurrent request.

column process heading “FNDLIBR PID”

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

v$process c,

v$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id = &Request_ID

AND a.phase_code = ‘R’;

You need your concurrent request ID as an input.

c.SPID= is the operating system process id

d.sid= is the Oracle process id



The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:

afimchk.sql Tells the status of the ICM and PMON method

afcmstat.sql Lists active manager processes

afrqrun.sql Lists all the running, waiting and Terminating requests

afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.

afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.

afcmcreq.sql Prints the log file name of the manager that processed the request

afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.

afimlock.sql Lists locks that the ICM is waiting to get

afcmrrq.sql Lists managers that currently are running a request