Oracle EBS DBA SQL Scripts

-View all the responsibilities of EBS users:

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

----The existing request time sort

SELECT fcr.request_id request_id,
TRUNC ((fcr.actual_completion_date-fcr.actual_start_date)/
(1/24)) *) 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)) *) > 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)) *) desc;

--Querying the versions of each module

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;

--Check Scheduled Tasks

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;

--View User login status

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

Oracle EBS DBA SQL Scripts