oracle EBS dba SQL scripts

標籤：oralce ebs

-查看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

oracle EBS dba SQL scripts