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

150次閱讀

共計 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 不能禁用只能修改密碼