Wednesday, May 9, 2012

Patch & their Sessions Monitor And All

Patching
========

select patch_level from fnd_product_installations where patch_level like '%AD%';
select bug_number,creation_date from ad_bugs where bug_number ='&a';
select PATCH_RUN_BUG_ID,BUG_ID,LAST_UPDATE_DATE,ORIG_BUG_NUMBER from AD_PATCH_RUN_BUGS where BUG_ID='&a';

select apd.driver_file_name, apd.DRIVER_TYPE_C_FLAG "C", apd.DRIVER_TYPE_D_FLAG "D", apd.DRIVER_TYPE_G_FLAG "G" ,
       apdl.language from ad_patch_drivers apd, ad_patch_driver_langs apdl where apd.patch_driver_id=apdl.patch_driver_id and
       apd.driver_file_name like '%4638432%';

select decode(status,'P', 'processed', 'U','unprocessed'),status ,count(*)from ad_parallel_update_units where update_id in
       (select update_id  from  ad_parallel_updates where script_name like '%GM%'  ) group by status;      

Blocks and Waits
===================
select session_id from dba_locks where blocking_others='Blocking';
select sid,event,seconds_in_wait from v$session_Wait where sid in (select sid from v$session where username='SYS');
select sid,decode(block,0,'NO','YES') BLOCKER,decode(request,0,'NO','YES') WAITER from v$lock where request > 0 or block > 0 order by block desc;
select * from v$lock where block>0;
select session_id,object_name, status, locked_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and object_name = 'CM_CMPT_DTL';
      
Sessions
=========
select s.sid,s.serial#,status,s.username,s.osuser from v$session s, v$process p where s.paddr=p.addr and p.SPID in (7062,25489,28583) order by status;
select 'alter system kill session '''|| sid||','||serial#||''';' from gv$session where status='INACTIVE'
select a.sid, a.serial#,b.sql_text from v$session a, v$sqlarea b where a.sql_address=b.address;
select SOFAR,TOTALWORK from v$session_longops where sid='&SID';

Active sessions from a particular machine for a particular database user
======================================
select sid,SERIAL#,server,MACHINE,TERMINAL from v$session where username= 'SCOTT';
ALTER SYSTEM KILL SESSION '1879,17' IMMEDIATE;
SELECT sid,serial# FROM v$session WHERE audsid = userenv('1131');
SELECT userenv('1051') FROM dual;

Patch restart process in case of a hang
----------------------------------------------------------------------
create table fnd_install_processes_bkp as select * from fnd_install_processes;
create table ad_deferred_jobs_bkp as select * from ad_deferred_jobs;
select WORKER_ID,CONTROL_CODE,STATUS from fnd_install_processes;
update fnd_install_processes set STATUS='F' where WORKER_ID>0 and STATUS='R';


ad_task_timing
------------

SELECT * FROM ad_task_timing WHERE start_time > SYSDATE - 10;

SELECT t.*,
to_char(t.start_time,'dd/mm/yy hh24:mi:yy'),
to_char(t.restart_time,'dd/mm/yy hh24:mi:yy'),
to_char(t.end_time,'dd/mm/yy hh24:mi:yy')
FROM ad_task_timing t
WHERE t.start_time > SYSDATE - 10;


select decode(a.patch_name,'merged',d.bug_number,a.patch_name) Patch_Number
      ,b.patch_driver_id,b.last_update_date,
      nvl(b.patch_abstract,c.patch_abstract)
from ad_applied_patches a,
     ad_patch_drivers b,
     ad_comprising_patches c,
     ad_bugs d
where a.creation_date > (sysdate-120)
and a.applied_patch_id=b.applied_patch_id
and b.patch_driver_id = c.patch_driver_id (+)
and c.bug_id = d.bug_id (+)
order by 3 desc;

No comments:

Post a Comment