Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Syed Saad Ali

Oracle ACE Pro

Oracle Solution Architect

Oracle E-Business Suite

Oracle Cloud Infrastructure

Oracle Fusion Middleware

Oracle Database Administration

Oracle Weblogic Administration

Blog Post

Application DBA Queries

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”

 

 

Related Posts
Write a comment