Get all the views that refer a particular table:
SELECT
UNION
SELECT o.object_name, o.status, o.last_ddl_time
FROM SYS.dba_objects o
WHERE o.object_type = 'VIEW'
AND EXISTS (
SELECT 'x'
FROM SYS.dba_dependencies d
WHERE d.referenced_type = 'TABLE'
AND d.TYPE = 'VIEW'
AND d.owner = 'APPS'
AND d.NAME = o.object_name
Get all the tables with the column name:
SELECT
TABLE_NAME
FROM
all_tab_columns
WHERE
column_name = '<COLUMN_NAME>'
Get SQL help from the Data base itself:
If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base
In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.
select *
from system.help
where upper(topic)=upper('string')
Find from which responsibility a concurrent program can be run:
SELECT distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
FROM
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
WHERE
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
Find all running concurrent requests with their run times:
The below query is useful for analyzing concurrent program run times and identifying their performance
select request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.actual_start_date > sysdate - 1
order by actual_completion_date - actual_start_date desc
Search all packages for a line of code:
SELECT *
FROM ALL_SOURCE
WHERE TYPE IN ('PACKAGE', 'PACK AGE BODY')
AND TEXT LIKE '%XXXX%'
The below table shows all the data base object information:
select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'
Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name
select fct.user_concurrent_program_
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_METH OD_CODE'
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_
List of all active workflow users and their roles
select wu.name user_name,
wr.name role_name
from wf_users wu,
wf_user_roles wur,
wf_roles wr
where wu.name = wur.user_name
and wur.role_name = wr.name
and wu.status = 'ACTIVE'
and wr.status = 'ACTIVE'
and wr.orig_system = 'WF_LOCAL_ROLES'
order by wu.name,
wr.name
Concurrent requests raised in the last day
select request_id,
decode(parent_request_id, -1, 'None') as parent_request_id,
fcpt.user_concurrent_program_name,
decode( fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') as status,
decode( fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') as phase,
fu.user_name,
fr.responsibility_name,
completion_text,
argument_text,
request_date,
requested_start_date,
actual_start_date,
actual_completion_date,
fcp.concurrent_program_name,
fe.executable_name,
fe.execution_file_name
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_executables fe,
fnd_responsibility_vl fr,
fnd_user fu
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcp.executable_id = fe.executable_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and fcr.request_date > sysdate - 1
--and fcr.concurrent_program_id = XXXX
order by request_id desc
How to find the Trace file generated for a concurrent program
Check out the following blog post for enabling the trace file for a concurrent program.
You can use the following query to locate the trace file, it takes as input the ' Concurrent Request id'
SELECT
'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
Removing Table Lock
Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
This script will report the SQL text of some of the locks currently being held in the database
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:
SELECT fu.user_id,
fu.user_name,
fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu,
fr.responsibility_name
FROM fnd_user fu,
fnd_user_resp_groups fur,
fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_
order by user_nameWHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_
Script to find Oracle API's for any module:
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
Query used for audit point of view i.e. when a profile is changed and by which user
SELECT t.user_profile_option_name,
profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = :p_value
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)
Check Current Applied Patch
SELECT patch_name, patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
Query used to view the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
This query will shows concurrent program processing time
SELECT f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio
n||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
View all types of request Application wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
Script to display status of all the Concurrent Managersselect distinct Concurrent_Process_Id CpId, PID Opid,Os_Process_ID Osid,
Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
Print Oracle Apps versions
SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')
