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

查看当前正在运行的报表(如果查不到，则把这行注释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有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

相关推荐: ​PostgreSQL如何解析查询语句中的表达式列并计算得出该列的值

这篇文章主要介绍PostgreSQL如何解析查询语句中的表达式列并计算得出该列的值，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！表达式列是指除关系定义中的系统列/定义列之外的其他投影列.比如:该SQL语句中的”1+id”投影列视为表达式…