Top 10 Useful oracle apps queries for DBA



Oracle apps DBA has to often trouble shoot various issues. I am giving here Top 10 Useful oracle apps queries for DBA

To find forms sesssion in database

set linesize 1000
column username format a8 heading “User|Name”
column sid format 9999
column serial# format 99999
column user_form_name format a25
column spid format a5
column unix_process format a7 heading “UNIX|Process”
column user_name format a15 trunc heading “USER NAME”
column Responsibility_name format a20 heading “Responsibility |Name”
select
s.sid,
s.serial#,
s.audsid,
s.username,
s.process unix_process,
p.spid,
u.user_name,
f.user_form_name,
to_char(a.start_time ,’DD-MON-YY’) start_time,
f.responsibility_name
from
v$process p,
v$session s,
fnd_form_sessions_v f,
applsys.fnd_logins a,
applsys.fnd_user u
where p.addr = s.paddr
and p.spid = f.PROCESS_SPID (+)
and s.process=a.spid
and p.pid=a.pid
and a.user_id=u.user_id
order by 1
/

To find application information

set linesize 1000
column application_short_name format a15 heading “APPL|SHORT_NAME”
column product_version format a15 heading “PRODUCT|VERSION”
column oracle_username format a12 heading “ORACLE|USER”
select
a.APPLICATION_SHORT_NAME,
a.BASEPATH,
p.PRODUCT_VERSION,
o.ORACLE_USERNAME,
p.PATCH_LEVEL
from
fnd_application a,
fnd_product_installations p,
fnd_oracle_userid o
where
a.application_id = p.application_id and
p.oracle_id = o.oracle_id
/

To find package info
select text
from user_source
where name=’&package_name’ and
text like ‘%$Header%’
/

To determine active JDBC sessions
col program format a40
col module format a40
select sid,serial#,module,program,sql_hash_value,last_call_et from v$session where status = ‘ACTIVE’and program like ‘JDBC%’
order by sql_hash_value
/

To determine dependency
set linesize 1000
column object_name format a40
column owner format a15
select owner,object_name,object_type,status, to_char (LAST_DDL_TIME,’DD-MON-YYYY HH:MI:SS’) LAST_DDL_TIME from dba_objects
where object_name IN (SELECT referenced_name FROM dba_dependencies WHERE name = ‘&Invalid_Object_Name’);

set linesize 1000
column object_name format a40
column owner format a15
select owner,object_name,object_type,status, to_char (LAST_DDL_TIME,’DD-MON-YYYY HH:MI:SS’) LAST_DDL_TIME
from dba_objects where object_name =upper (‘&object_name’);

set linesize 1000
column application_id format a999999 heading “APPL|ID”
column application_short_name format a10 heading “APPL|SHORT NAME”
column application_name format a50 heading “APPLICATION NAME”
column language format a4 heading “LANG”
select a.application_id,
a.application_short_name,
a.basepath,
at.application_name
from fnd_application a,
fnd_application_tl at
where at.application_id = a.application_id
and at.language=’US’
group by a.application_id,a.application_short_name,a.basepath,at.application_name,at.language
order by a.application_id
/

Profile Related queries

Site level profile

select a.PROFILE_OPTION_ID,a.PROFILE_OPTION_NAME,b.USER_PROFILE_OPTION_NAME,c.PROFILE_OPTION_VALUE ,c.level_id
from fnd_profile_options a,fnd_profile_options_tl b ,fnd_profile_option_values c where a.PROFILE_OPTION_NAME=b.PROFILE_OPTION_NAME and a.PROFILE_OPTION_ID=c.PROFILE_OPTION_ID
and b.LANGUAGE=’US’ and b.USER_PROFILE_OPTION_NAME like ‘&1’;

Application level

select a.PROFILE_OPTION_ID,a.PROFILE_OPTION_NAME,b.USER_PROFILE_OPTION_NAME,c.PROFILE_OPTION_VALUE from fnd_profile_options a,fnd_profile_options_tl b ,fnd_profile_option_values c where a.PROFILE_OPTION_NAME=b.PROFILE_OPTION_NAME and a.PROFILE_OPTION_ID=c.PROFILE_OPTION_ID and b.LANGUAGE=’US’ and c.LEVEL_ID=10002 and c.level_value=’&2′ b.USER_PROFILE_OPTION_NAME like ‘&1’;

Applications

select a.APPLICATION_ID,a.APPLICATION_SHORT_NAME,a.BASEPATH,b.APPLICATION_NAME from fnd_application a,fnd_application_tl b where a.APPLICATION_ID=b.APPLICATION_ID and b.LANGUAGE=’US’ and b.APPLICATION_NAME like ‘&1’;

Compile code of package and package body

set pages 999
set head off
select
‘alter ‘||decode(object_type,’PACKAGE BODY’,’PACKAGE’,
‘VIEW’,’VIEW’,object_type)
||’ ‘||object_name||’ compile ‘||
decode (object_type,’PACKAGE BODY’,’body’)||’;’ from dba_objects
where status=’INVALID’
order by decode(object_type,’PACKAGE’,1,’VIEW’,2,’PACKAGE BODY’,3,4)
/

column owner format a5
compute sum of “TOTAL” on report
SELECT owner,object_type,COUNT(*)”TOTAL” FROM DBA_OBJECTS WHERE status=’INVALID’
and owner=’APPS’ GROUP BY owner,object_type ;

and status =’ACTIVE’

To find out the passwords of schema using apps password :-

Set serveroutput on
declare
sche_var varchar2(2000);
begin
sche_var:=fnd_oracle_schema.getopvalue(‘schema_name’,’apps password’);
dbms_output.put_line(sche_var);
end;
/

Related Articles

Every thing You must know about Profile options in Oracle Apps

60 Awesome oracle apps dba interview questions

Most commmonly Frequently asked questions for DBA

40 Adpatch question every DBA should know

19 oracle apps technical interview questions and answers

29 Oracle apps interview questions and answer




Leave a Reply