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”