Queries Arsenel for Oracle Apps DBA - Part 8

Queries Arsenel for Oracle Apps DBA - Part 8

-- Responsibilities attached to User

 
SELECT

 
substr(fr.responsibility_name,1,35) Responsibility

 
,fu.user_name

 
,fur.end_date

 
FROM

 
apps.fnd_user fu,

 
apps.fnd_user_resp_groups_direct fur,

 
apps.fnd_responsibility_vl fr,

 
apps.fnd_application_vl fa

 
WHERE

 
fur.user_id = fu.user_id

 
AND fu.user_name NOT IN ('YOUR_DBA_NAME')

 
AND fur.responsibility_application_id = fa.application_id

 
AND fur.responsibility_application_id = fr.application_id

 
AND fur.responsibility_id = fr.responsibility_id

 
AND fu.end_date is null -- only show active users

 
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment

 
AND fr.responsibility_name in ('System Administrator'

 
,'Application Developer'

 
,'Application Developer Common Modules'

 
,'CRM HTML Administration'

 
,'FICO Sys Admin Conversion'

 
,'FICO System Administration GUI'

 
,'Functional Administrator' -- can chg Profiles

 
,'Functional Developer'

 
,'Knowledge Administrator' --use FICO instead

 
,'Oracle Sales Administrator' --use FICO instead

 
,'Sales Administrator' --use FICO instead

 
,'SFM System Administrator'

 
,'TeleSales Administration' --use FICO instead

 
)

 

 

 
--show currently logged in Forms connections, multiple connections per user

 
--approximately half as many Forms users as DB forms connections

 

 

 
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') rundatetime

 
,(SELECT COUNT(*)

 
FROM apps.fnd_logins fl -- only has Forms login rows

 
, apps.fnd_user fu

 
, v$session s

 
, v$process p

 
WHERE fl.end_time IS NULL

 
AND fl.user_id = fu.user_id(+)

 
AND fl.process_spid = p.spid

 
AND fl.pid = p.pid

 
AND fl.serial# = p.serial#

 
AND p.addr = s.paddr

 
AND s.program IS NULL) formsusers

 
--

 
--

 
-- SelfServ Activity last 60 min based on icx_sessions

 
,(SELECT COUNT(*)

 
FROM apps.icx_sessions icx

 
, apps.fnd_user fu

 
, apps.fnd_responsibility_vl frv

 
WHERE icx.user_id = fu.user_id(+)

 
AND icx.disabled_flag = 'N'

 
AND icx.last_connect > SYSDATE - 1/24

 
--Preferences

 
AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)

 
AND icx.responsibility_application_id = frv.application_id(+)

 
-- only web self service responsibilities

 
AND frv.version(+) = 'W') selfservusers

 
--

 

 

 
--Terminated Users with Active account in E-BIZ

 

 

 
SELECT DISTINCT(fu.user_name)

 
, papf.employee_number emp_num

 
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') term_date

 
, period_of_service_id per_serv_id

 
FROM apps.fnd_user fu

 
, apps.per_all_people_f papf

 
, apps.per_periods_of_service ppos

 
WHERE fu.employee_id = papf.person_id

 
AND papf.person_id = ppos.person_id

 
AND fu.end_date IS NULL

 
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)

 
FROM per_periods_of_service

 
WHERE person_id = papf.person_id)

 
AND ppos.actual_termination_date < TRUNC(SYSDATE)

 

 

 
--GSM Enabled or not

 

 

 
Select 'GSM' "Setting", NVL(v.profile_option_value, 'N') "Status"

 
from fnd_profile_options p, fnd_profile_option_values v

 
where p.profile_option_name = 'CONC_GSM_ENABLED'

 
and p.profile_option_id = v.profile_option_id;

 

 

 
--RAC ENABLED or NOT

 
Select 'RAC' "Setting", decode(count(*), 0, 'N', 1, 'N', 'Y') "Status" from V$thread

 

 

 
--PCP ENABLED or NOT

 
Select 'PCP' "Setting", value "Status" from fnd_env_context where variable_name = 'APPLDCP'

 
and concurrent_process_id = (select max(concurrent_process_id) from fnd_concurrent_processes

 
where concurrent_queue_id = 1)

 

 

 
--GSM Enabled Processes running on different node.

 
Select n.node_name "Node", NVL(l.meaning, n.node_mode) "Mode",

 
n.status "Status",

 
count(p.concurrent_process_id) "Current GSM Processes"

 
from fnd_nodes n, fnd_concurrent_processes p, fnd_lookups l

 
where p.node_name(+) = n.node_name

 
and p.process_status_code(+) not in ('S', 'K')

 
and l.lookup_type(+) = 'CP_NODE_MODE'

 
and l.lookup_code(+) = n.node_mode

 
group by n.node_name, NVL(l.meaning, n.node_mode), n.status;