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.

About the Author Atul Kumar

Leave a Comment:

5 comments
ABhishek Mishra says March 1, 2009

Hi,

I want to know dat invalid object effect system performance ?Please clarify how INvalid object effect our database.ALso let me how to delete portal30 invalid object from databse

Rgrds,
ABhishek

Reply
Ather Hussain says July 20, 2009

Hi

Can you send me step by step document to install R12 in RAC environment.

I;m goog in RAC admin and R12 application dba but I don’t know how to configure RAC in R12 Application.

Kindly help

Ather Hussain
Dubai
0091 0552334628

Reply
Atul Kumar says July 20, 2009

@ Ather ,

Check beklow documentation

Metalink Note

783044.1 Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
745759.1 Oracle E-Business Suite and Oracle Real Application Clusters Documentation Roadmap

Oracle Documentation for 11g RAC
http://download.oracle.com/docs/cd/B28359_01/rac.111/b28254/admcon.htm#i1058057

Migration to ASM
http://www.oracle.com/technology/deploy/availability/pdf/Technical_WP_ASM_Migration.pdf

Reply
Ather Hussain says July 20, 2009

Thanks a lot! Atul for your quick reply

Reply
Paul says June 22, 2010

Hi Ramnik,

Thanks for the info.

Do you have any script to find out any locked objects in the database?

Regards.

Reply
Add Your Reply

Not found