EBS DBA日常維護使用的sql有哪些

這篇文章給大家分享的是有關EBS DBA日常維護使用的sql有哪些的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

查詢應用服務器的會話數
select 'Number of user sessions : ' || count( distinct session_id) 
How_many_user_sessions 
from icx_sessions icx 
where disabled_flag != 'Y' 
and PSEUDO_FLAG = 'N' 
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL, 
limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate 
and counter < limit_connects;


查看當前正在運行的報表(如果查不到，則把這行注釋and a.phase_code in ('I','P','R','T'))
select
       q.concurrent_queue_name qname
      ,f.user_name || ': ' || f.description
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,decode(a.phase_code,'C','Completed','I','Inactive','P','Pending','R','Running',a.phase_code) "Phase_Code"
      ,decode(a.status_code,'G','Warning','C','Normal','E','Error','R','Normal','D','Cancelled','I','Scheduled',
              'X','Terminated',a.status_code) "Status_Code"
--      ,b.os_process_id "OS"
--      ,vs.sid
--      ,vs.serial# "Serial#"
--      ,vp.spid
      ,a.oracle_process_id "spid"
      ,a.actual_start_date
      ,a.actual_completion_date
      ,round((nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440,2) "Time--Minutes"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name||' '||a.description "Program"
      ,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
--    ,v$session vs
--    ,v$process vp
where
      a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and c2.application_id = c.application_id
  and a.phase_code in ('I','P','R','T')
  and a.requested_by = f.user_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'


查看報表名稱為CUX:下單明細表最近100天的運行情況
select
      -- q.concurrent_queue_name qname
      f.user_name || ': ' || f.description
      ,a.request_id "Req Id"
    --  ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,decode(a.phase_code,'C','Completed','I','Inactive','P','Pending','R','Running',a.phase_code) "Phase_Code"
      ,decode(a.status_code,'G','Warning','C','Normal','E','Error','R','Normal','D','Cancelled','I','Scheduled',
              'X','Terminated',a.status_code) "Status_Code"
--      ,b.os_process_id "OS"
--      ,vs.sid
--      ,vs.serial# "Serial#"
--      ,vp.spid
      ,a.oracle_process_id "spid"
      ,a.actual_start_date
      ,a.actual_completion_date
      ,round((nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440,2) "Time--Minutes"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name||' '||a.description "Program"
       ,a.phase_code
      ,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
--    ,v$session vs
--    ,v$process vp
where
      a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and c2.application_id = c.application_id
 -- and a.phase_code in ('I','P','R','T')
  and a.requested_by = f.user_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
  and c2.user_concurrent_program_name='CUX:下單明細表'
  and a.actual_start_date>sysdate-30
  order by a.actual_start_date


--根據Request ID找到對應的Session信息:
select *
from v$session
where paddr in
(select addr
from v$process
where spid =
(select b.oracle_process_id
from apps.fnd_concurrent_requests b
where b.request_id = &req_id))

--查詢默認應用用戶，比如是否需要鎖定、修改這些用戶
SELECT ROWID
      ,user_name
      ,description
      ,password_lifespan_days
      ,password_lifespan_accesses
      ,email_address
      ,fax
      ,start_date
      ,end_date
      ,user_id
      ,last_logon_date
      ,encrypted_foundation_password
      ,encrypted_user_password
      ,password_date
      ,password_accesses_left
      ,employee_id
      ,customer_id
      ,supplier_id
      ,person_party_id
      ,session_number
      ,last_update_date
      ,last_updated_by
      ,created_by
      ,creation_date
      ,last_update_login
  FROM apps.fnd_user
 WHERE user_name IN ('AME_INVALID_APPROVER','ANONYMOUS','APPSMGR','ASADMIN','ASGADM','ASGUEST','AUTOINSTALL','CONCURRENT MANAGER','FEEDER SYSTEM',
'GUEST','IBE_ADMIN','IBE_GUEST','IBEGUEST','IEXADMIN','INDUSTRY DATA','INITIAL SETUP','IRC_EMP_GUEST','IRC_EXT_GUEST','MOBILEADM','MOBILEDEV','OP_CUST_CARE_ADMIN',
'OP_SYSADMIN','ORACLE12.0.0','ORACLE12.1.0','ORACLE12.2.0','ORACLE12.3.0','ORACLE12.4.0','ORACLE12.5.0','ORACLE12.6.0','ORACLE12.7.0','ORACLE12.8.0','ORACLE12.9.0','PORTAL30',
'PORTAL30_SSO','STANDALONE BATCH PROCESS','SYSADMIN','WIZARD','XML_USER') and end_date is null order by 1
--WEB窗口查不到的用戶是ANONYMOUS、AUTOINSTALL、INITIAL SETUP、FEEDER SYSTEM、CONCURRENT MANAGER、STANDALONE BATCH PROCESS，所以這些用戶無法自己手工禁用即在web頁面吧end_date設置為null，但是這幾個用戶的end_date系統已經默認為1951/1/1即已經自動禁用，只有AUTOINSTALL在上述語句中能查到，所以AUTOINSTALL不能禁用只能修改密碼

感謝各位的閱讀！關于“EBS DBA日常維護使用的sql有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！