oracle EBS dba SQL scripts

-查看EBS用户的所有职责：

SELECT frt.responsibility_name, furg.END_DATE
FROM fnd_user_resp_groups furg,
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_user fu
where fu.user_name = '&username'
and fu.user_id=furg.user_id
and furg.RESPONSIBILITY_ID = fr.responsibility_id
and frt.responsibility_id=fr.responsibility_id
--and furg.END_DATE is not null
order by 1

----现有的请求时间排序

SELECT fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date - fcr.actual_start_date) /
(1 / 24)) * 60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE TRUNC(((fcr.actual_completion_date - fcr.actual_start_date) /
(1 / 24)) * 60) > NVL('&min', 45)
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcpt.application_id
and fcpt.language = USERENV('Lang')
ORDER BY TRUNC(((fcr.actual_completion_date - fcr.actual_start_date) /
(1 / 24)) * 60) desc;

--查询各个模块的版本

select a.oracle_id,
a.last_update_date,
a.product_version,
a.patch_level,
decode(a.status,
'I',
'Installed',
'S',
'Shared',
'N',
'Not Installed',
a.status) Status,
a.industry,
b.application_name,
c.application_short_name
from fnd_product_installations a, fnd_application_tl b, fnd_application c
where a.application_id = b.application_id
and a.application_id = c.application_id
and b.language = 'US'
order by c.application_short_name;

--检查定时任务

select
fcr.request_id,
fcr.parent_request_id,
fu.user_name requestor,
to_char(fcr.requested_start_date, 'MON-DD-YYYY HH24:MM:SS') START_DATE,
fr.responsibility_key responsibility,
fcp.concurrent_program_name,
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') status,
decode(fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') phase,
fcr.completion_text
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu,
fnd_responsibility fr
where
fcr.status_code in ('Q', 'I') and
fcr.hold_flag = 'N' and
fcr.requested_start_date > sysdate and
fu.user_id = fcr.requested_by and
fcr.concurrent_program_id = fcp.concurrent_program_id and
fcr.concurrent_program_id = fcpt.concurrent_program_id and
fcr.responsibility_id = fr.responsibility_id
order by
fcr.requested_start_date, fcr.request_id;

--查看用户登录情况

SELECT user_name username,
description name,
to_char(b.first_connect, 'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect, 'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN(first_connect) first_connect,
MAX(last_connect) last_connect,
last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect > SYSDATE - 3 / 12
ORDER BY 4 DESC