Oracle Apps DBA has too often troubleshoot various issues. I am giving here the Top 30 Useful oracle apps queries for DBA
Table of Contents
how to find form session id in oracle
The below query will provide the session details for all the forms opened in the applications
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 /
If we are looking for a session id for the particular form process, then use the below query
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 and a.spid= &forms_process order by 1 / or column "User Name" format a10 column "ClPID" format a8 select d.user_name "User Name", b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID", to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime" from fnd_logins a, v$session b, v$process c, fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = 'USER_NAME' OR 1=1) and a.SPID = &PID /
How to find application information
The below query will help you find the information on applications in Oracle EBS
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 /
query to check form personalization in oracle apps
SELECT DISTINCT A.FORM_NAME,
A.FUNCTION_NAME,
A.ENABLED,
C.USER_FORM_NAME,
D.APPLICATION_NAME,
A.SEQUENCE,
A.TRIGGER_EVENT,
A.DESCRIPTION personalization_rule_name,
A.CONDITION
FROM FND_FORM_CUSTOM_RULES A,
FND_FORM B,
FND_FORM_TL C,
FND_APPLICATION_TL D
WHERE ENABLED = 'Y'
AND A.FORM_NAME = B.FORM_NAME
AND B.FORM_ID = C.FORM_ID
AND B.APPLICATION_ID = D.APPLICATION_ID
ORDER BY APPLICATION_NAME;
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');
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';
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 the schema using apps password
Suppose you know the apps password but you don’t know the Product schema password, then you can use the below query to get the product schema 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;
What is the File version in database/sql query to find file version in oracle apps
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 /
query to check version of package in oracle apps
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>')
;
Query to get FND messages in Oracle Apps
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
/
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
Log in 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 the 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, and 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 within 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 the 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, log in 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');
How to find the responsibilities assigned to a Particular User
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from
fnd_user_resp_groups_direct furg,
fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = upper('&user_name')
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');
How to find Menu, Sub-menu and Functions in Oracle apps
SELECT second.application_id "App ID" , second.application_name "App Name" , second.responsibility_id "Resp ID" , second.responsibility_name "Responsibility" , second.menu_id "Menu ID" , second.user_menu_name "Main Menu Name" , second.entry_sequence "Seq" , second.prompt "Prompt" , second.function_id "Function ID" , second.user_function_name "Function" , second.func_descrip "Function Descrip" , second.sub_menu_id "SubMenu ID" , second.sub_menu_name "SubMenu Name" , second.sub_seq "Sub Seq" , second.sub_prompt "SubPrompt" , second.sub_func_id "SubFunction ID" , second.sub_func "SubFunction" , second.sub_func_descrip "SubFunction Descrip" , second.sub_sub_menu_id "Sub-SubMenu ID" , second.grant_flag "Grant Flag" , second.resp_end_date "Resp End Date" , DECODE( exc.rule_type , 'F', ( SELECT 'Ex F: ' || exc.action_id FROM fnd_form_functions_vl fnc WHERE fnc.function_id = exc.action_id AND second.function_id = exc.action_id ) ) excluded_function , DECODE( exc.rule_type , 'F', ( SELECT 'Ex SF: ' || exc.action_id FROM fnd_form_functions_vl fnc WHERE fnc.function_id = exc.action_id AND second.sub_func_id = exc.action_id ) ) excluded_sub_function , DECODE( exc.rule_type , 'M', ( SELECT 'Ex M: ' || exc.action_id FROM fnd_form_functions_vl fnc WHERE fnc.function_id = exc.action_id AND second.menu_id = exc.action_id ) ) excluded_menu , DECODE( exc.rule_type , 'M', ( SELECT 'Ex SM: ' || exc.action_id FROM fnd_form_functions_vl fnc WHERE fnc.function_id = exc.action_id AND second.sub_menu_id = exc.action_id ) ) excluded_sub_menu , DECODE( exc.rule_type , 'M', ( SELECT 'Ex SSM: ' || exc.action_id FROM fnd_form_functions_vl fnc WHERE fnc.function_id = exc.action_id AND second.sub_sub_menu_id = exc.action_id ) ) excluded_sub_sub_menu FROM ( SELECT FIRST.application_id , FIRST.application_name , FIRST.responsibility_id , FIRST.responsibility_name , FIRST.end_date AS resp_end_date , FIRST.menu_id , FIRST.user_menu_name , FIRST.entry_sequence , FIRST.prompt , FIRST.function_id , ffft.user_function_name , ffft.description AS func_descrip , FIRST.sub_menu_id , fmv2.user_menu_name AS sub_menu_name , fme2.entry_sequence AS sub_seq , fmet2.prompt AS sub_prompt , fme2.function_id AS sub_func_id , ffft2.user_function_name AS sub_func , ffft2.description AS sub_func_descrip , fme2.sub_menu_id AS sub_sub_menu_id , FIRST.grant_flag FROM ( SELECT fat.application_id , fat.application_name , fr.responsibility_id , frt.responsibility_name , fr.end_date , fr.menu_id , fmv.user_menu_name , fme.entry_sequence , fmet.prompt , fme.sub_menu_id , fme.function_id , fme.grant_flag FROM apps.fnd_application_tl fat , apps.fnd_responsibility fr , apps.fnd_menus_vl fmv , apps.fnd_responsibility_tl frt , apps.fnd_menu_entries fme , apps.fnd_menu_entries_tl fmet WHERE fat.application_id = fr.application_id(+) AND fr.menu_id = fmv.menu_id(+) AND fr.responsibility_id = frt.responsibility_id(+) AND fr.menu_id = fme.menu_id(+) AND fme.menu_id = fmet.menu_id(+) AND fme.entry_sequence = fmet.entry_sequence(+) AND fmet.language = 'US' AND fat.application_id = &appid ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST , apps.fnd_menus_vl fmv2 , apps.fnd_menu_entries fme2 , apps.fnd_menu_entries_tl fmet2 , apps.fnd_form_functions_tl ffft , apps.fnd_form_functions_tl ffft2 WHERE FIRST.function_id = ffft.function_id(+) AND FIRST.sub_menu_id = fmv2.menu_id(+) AND FIRST.sub_menu_id = fme2.menu_id(+) AND fme2.menu_id = fmet2.menu_id(+) AND fme2.entry_sequence = fmet2.entry_sequence(+) AND fme2.function_id = ffft2.function_id(+) ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) second LEFT OUTER JOIN apps.fnd_resp_functions exc ON ( second.application_id = exc.application_id AND second.responsibility_id = exc.responsibility_id AND ( second.function_id = exc.action_id OR second.sub_func_id = exc.action_id OR second.menu_id = exc.action_id OR second.sub_menu_id = exc.action_id OR second.sub_sub_menu_id = exc.action_id ) ) ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21;
query to find the form path in oracle apps
SELECT
a.form_name, a.user_form_name, a.description,b.application_name,c.basepath,
FROM
fnd_form_vl a,
fnd_application_tl b,
fnd_application c
WHERE
a.application_id=b.application_id
b.application_id=c.application_id
(form_id >= 0) AND UPPER (user_form_name) LIKE '%&1%';
I have tested these queries on an environment and seem to be working. But there could be still some mistakes. Please do comments in case you find any issues or mistakes.
I hope you find these oracle apps queries useful in your day-to-day work and it helps in resolving the issues
Related Articles
Oracle Apps Profile options
oracle apps dba interview questions
Oracle DBA Interview Questions
40 Adpatch question every DBA should know
Oracle apps interview questions and answer
https://en.wikipedia.org/wiki/Oracle_Application
Hi , These are great blogs, its really good to find such enthusiasm for EBS and also helping others. We have a very handy free to use java form in EBS (same look and feel) reporting tool for you to host your SQL’s on and create rapid shareable Requests without the necessity to register each as a conc program but still using the conc request / manager to run directly to Excel.
For DB tuning and or AOL SQL’s – see our share lib at – https://www.enginatics.com/library/?pg=1&app%5B%5D=Application%20Object%20Library
You can also download the freeware version of Blitz Report which has a number of SQL reports similar to yours, or try for yourself on our demo instance with username: developer and password = blitz
Great work
Glen