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