Application DBA Queries
QUERY TO GET all user assigned to GIVEN RESPONSIBILITY
SELECT UNIQUE u.user_id,
Substr(u.user_name, 1, 30) user_name,
Substr(r.responsibility_name, 1, 60) responsiblity,
Substr(a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND r.responsibility_name = ‘System Administrator’
–“Write Responsibility Name Here “
ORDER BY Substr(user_name, 1, 30),
Substr(a.application_name, 1, 50),
Substr(r.responsibility_name, 1, 60);
Query to get all Responsibility assigned to a user
SELECT UNIQUE u.user_id,
Substr (u.user_name, 1, 30) user_name,
Substr (r.responsibility_name, 1, 60) responsiblity,
Substr (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND u.user_name = ‘SYED.SALI’ –“Write Application User Name Here “
ORDER BY Substr (user_name, 1, 30),
Substr (a.application_name, 1, 50),
Substr (r.responsibility_name, 1, 60);
QUERY FOR Workflow Mailer Configurations
SELECT p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘OUTBOUND_SERVER’, ‘INBOUND_SERVER’, ‘ACCOUNT’,
‘FROM’,
‘NODENAME’, ‘REPLYTO’, ‘DISCARD’, ‘PROCESS’,
‘INBOX’ )
ORDER BY p.parameter_name;
QUERY TO FIND WHO is connected when to which responsibility
SELECT DISTINCT fu.user_name User_Name, fr.responsibility_key Responsibility FROM fnd_user fu, fnd_responsibility fr, icx_sessions ic WHERE fu.user_id = ic.user_id AND fr.responsibility_id = ic.responsibility_id AND ic.disabled_flag = 'N' AND ic.responsibility_id IS NOT NULL AND ic.last_connect LIKE SYSDATE; -- "Enter the Date here"
QUERY TO FIND Number of user connected to EBS
SELECT Count (DISTINCT d.user_name)
FROM apps.fnd_logins a,
v$session b,
v$process c,
apps.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 );
QUERY TO FIND E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES
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’,
10005, ‘Server’,
10006, ‘Org’,
10007, Decode(To_char(v.level_value2), ‘-1’,
‘Responsibility’
,
Decode(To_char(v.level_value), ‘-1’, ‘Server’,
‘Server+Resp’)),
‘UnDef’) LEVEL_SET,
Decode(To_char(v.level_id), ‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.name,
‘10007’,
Decode(To_char(v.level_value2), ‘-1’,
rsp.responsibility_key,
Decode(
To_char(v.level_value), ‘-1’, (SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2),
(SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2)
|| ‘-‘
||
rsp.responsibility_key)),
‘UnDef’) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND Upper(p.profile_option_name) IN
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE Upper(user_profile_option_name) LIKE
Upper(‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name,
user_profile_option_name,
level_id,
level_set;
QUERY TO FIND APPLICATION USER DETAILS
SELECT user_name,
user_id,
Decode(employee_id, NULL, NULL,
‘E’) employee_flag,
description,
start_date,
end_date,
Decode(end_date, Least(end_date, SYSDATE), NULL,
‘+’) f,
To_char(last_logon_date, ‘mm/dd/yy hh24:mi:ss’) last_logon_time,
Decode(end_date, NULL, SYSDATE – last_logon_date) last_logon_days,
Decode(end_date, NULL, Decode(30, Greatest(30, Trunc(
SYSDATE – last_logon_date))
, NULL,
‘*’)) p,
password_lifespan_days,
password_date,
Decode(end_date, NULL, SYSDATE – password_date) password_change_days
FROM applsys.fnd_user u
WHERE user_name LIKE Upper(‘%&userid%’) –“Write Application User Name Here “
ORDER BY description,
user_name;
QUERY TO FIND COMPLETE DATABASE SIZE
SELECT ‘DATA_n_INDEX: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM v$controlfile;
QUERY TO VERIFY THE WORKFLOW IMAP SETTING
SELECT c.component_id,
c.component_name,
p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘INBOUND_SERVER’, ‘ACCOUNT’, ‘REPLYTO’ )
ORDER BY c.component_id,
c.component_name,
p.parameter_name;
QUERY TO CHECK WEB SESSIONS
SELECT Count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
AND s.program = ‘JDBC Thin Client’;
QUERY TO FIND Global Locks
SELECT Count(*)
FROM gv$lock a
WHERE a.id1 IN (SELECT id1
FROM gv$lock
WHERE request <> 0);
QUERY to find out version of a PACKAGE
SELECT text
FROM dba_source
WHERE line = 2
AND name = ‘AP_IMPORT_INVOICES_PKG’; — “ENTER PACKAGE NAME HERE”