Top 5 Oracle apps queries for APPS Database | Oracle Database

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
/