EBS DBA日常维护使用的sql有哪些

65次阅读
没有评论

共计 4883 个字符,预计需要花费 13 分钟才能阅读完成。

这篇文章给大家分享的是有关 EBS DBA 日常维护使用的 sql 有哪些的内容。丸趣 TV 小编觉得挺实用的,因此分享给大家做个参考,一起跟随丸趣 TV 小编过来看看吧。

查询应用服务器的会话数
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 有哪些”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计4883字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)