Collection of Queries/Scripts

1)How to check if the partitions of a table are set to LOGGING

select partition_name, logging
from dba_tab_partitions
where table_name=’WF_LOCAL_ROLES’;

2)How to Correct Session Cookie Name.
a)select session_cookie_name from icx_parameters;

b)update icx_parameters set session_cookie_name = ‘<hostname_sid>’;

c)select session_cookie_name from icx_parameters;

3) 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

4) How to check which object is corrupted.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 64 and 1 between block_id AND block_id + blocks-1;

5) How to check whether the product is install,shared and Not installed in Apps.

select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I',’Fully Installed’,
‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;

6) How to check access level when label security feature is installed.

col  USER_NAME format a15
col POLICY_NAME format a15
col USER_PRIVILEGES format a15
col USER_LABELS format a20
select USER_NAME,POLICY_NAME,USER_PRIVILEGES,USER_LABELS from dba_sa_users
 where USER_NAME=’APPS’;

7) How to find out Summary of Concurrent requests.

SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10

8 ) How to find out Package Header.

select name,text from dba_source where text like ‘%Header: %’
and owner = ‘APPS’ and name = ‘INVALID_OBJECT_NAME’;

9) How to find out version of a package.

select text from dba_source
where line=2
and name=’AP_IMPORT_INVOICES_PKG’;

10) How to find out which request is handle by which concurrent queue.

a) First find out short_name of a program and then pass it as parameter to below query.

b) The below query will give you output
 I – Included  – Included in new concurrent queue
 E – excluded from Standard Manager

This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.

SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
 B.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id
/

11) How to backup the defination of a View before droping a view.

 select dbms_metadata.get_ddl(‘VIEW’,'RG_View’,'APPS’) from dual;

I will update some more scripts in my next post.