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.