Oracle apps queries for APPS DBA

To find forms session in Oracle 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
/

Oracle 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;
/

Script to find out patches applied

select orig_bug_number,to_char(max(last_update_date),'DD-MON-YYYY hh24:mi:ss') updated from ad_patch_run_bugs where
orig_bug_number in
(
select distinct app_patch.patch_name
from (
select substr(ap.patch_name,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_applied_patches ap
where pr.start_date between to_date('&&1','MM/DD/YYYY')
and to_date('&&2','MM/DD/YYYY')
and dr.patch_driver_id = pr.patch_driver_id
and ap.applied_patch_id = dr.applied_patch_id
and not exists
(select 'x' from ad_comprising_patches cp
where cp.patch_driver_id = dr.patch_driver_id)
UNION ALL
select substr(b.bug_number,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_comprising_patches cp, ad_bugs b
where pr.start_date between to_date('&&1','MM/DD/YYYY')
and to_date('&&2','MM/DD/YYYY')
and dr.patch_driver_id = pr.patch_driver_id
and cp.patch_driver_id = pr.patch_driver_id
and cp.bug_id = b.bug_id) app_patch)
group by orig_bug_number;

Version information

select name, created from v$database;
select * from product_component_version;
select RELEASE_NAME from FND_PRODUCT_GROUPS;

Materialized view

select unusable, known_stale, invalid, REWRITE_ENABLED, mview_name from
sys.dba_mview_analysis where MVIEW_NAME like 'MSC%';

What is File version in database

set pages 52
set verify off
set term on
set feedback off
col "APP NAME" format a10
col filename format a15
col version format a15
col last_update_date format a15
set linesize 90
select af.app_short_name "APP NAME",
af.filename,
afv.version,
afv.LAST_UPDATE_DATE
from ad_files af,
ad_file_versions afv
where af.file_id = afv.file_id and
af.filename like '&1'
order by afv.LAST_UPDATE_DATE desc
/

What is the package versions?

select text
from user_source
where line = 2
and name = upper('&enter_package_name')
order by line
/

Check XML file in MDS repository

exec JDR_UTILS.printDocument('/oracle/apps/fnd/framework/navigate/webui/<XYZ>')
;

Check messages

select message_text from fnd_new_messages m, fnd_application a
where upper('&message_name') = m.message_name
and upper('&language_code') = m.language_code
and upper('&application_short_name') = a.application_short_name
and m.application_id = a.application_id
/

Check profile options

set linesize 140
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_VAL format 999999999
column VALUE format A60 wrap

select
p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
'UnDef') LEVEL_SET,
v.level_value LEVEL_VAL,
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
/

How to compile the package,package body ,procedures

alter package <package name > compile;
alter package <package name > compile body;
alter procedure <procedure name > compile;

If it is not successfully compiled, we can look at the errors from the below sql
Login to SQL*Plus.

SELECT text
FROM dba_errors
WHERE name = packagename;

The output will look something like this.
TEXT
---------------------------------------------------------------------
PLS-00302: component 'XYZ' must be declared
PL/SQL: Statement ignored

How to find the Version Number for EBS filesystem

Version number helps establish what version of a file is being executed on the system. If the patch failure is with a package or package body, establish the version of the file that the patch installs in the patch/sql directory. The version information is viewed in the file Header information. Compare that version to the version that is compiled
into the database.

The database version can be found by logging in to SQL*PLUS and selecting:

SELECT text
FROM dba_source
WHERE name = package name
AND text like '%Header%';

To establish the version of other files such as forms, reports, sql use the strings command at the Unix command line:

strings -a filename | grep header -i

To find versions of Unix C code for a specific module, such as pyxy.lpc:

Strings PYUGEN | grep '$Header: pyxy.lpc'

To find versions of Unix C code for all the modules with in a C module

such as PYUGEN:
Strings PYUGEN | grep '$Header'

These can also be done with adident command

How to find the status of invalids in database

To verify objects that are invalid, login to SQL*PLUS as apps:

SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID';

How to find the trigger body

To see what a failing trigger is trying to do, login to SQL*PLUS:

Set long 20000;
SELECT table_name, trigger_name, trigger_body
FROM dba_triggers
WHERE table_name = upper ('table name');
or
WHERE trigger_name = upper ('trigger name');

How to find the information about constraints

For a check constraint error such as ORA_02290: check constraint
(HR.PAY_BCH_BATCH_STATUS_CHK) violated

Login to SQL*PLUS:

Set long 20000;
Select table_name, search_condition
From dba_constraints
Where constraint_name = upper ('pay_bch_batch_status_chk');

Related Articles

Oracle Apps Profile options
oracle apps dba interview questions
oracle DBA Interview questions
40 Adpatch question every DBA should know
19 oracle apps technical interview questions and answers
29 Oracle apps interview questions and answer