select * from ap_invoices_all where invoice_id=
EBS : How to cross check applcation name
select * from FND_Application_tl where application_name like 'Payable%'
select * from FND_Application where application_id=200
EBS : Find code level
select * from fnd_product_installations where patch_level like '%AD%';
select abbreviation, codelevel from ad_trackable_entities where abbreviation in ('txk','ad','atg_pf');
Oracle : Identify lock objects and sessions select session_id sid ,SERIAL# Serial , substr(object_name,1,20) Object1, substr(os_user_name,1,10) Terminal1, substr(oracle_username,1,10) Locker1, nvl(lockwait,'active') Wait1, decode(locked_mode, 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive', 'unknown') Lockmode , OBJECT_TYPE Type1 FROM SYS.V_$LOCKED_OBJECT A, SYS.ALL_OBJECTS B, SYS.V_$SESSION c WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID ORDER BY 1 ASC, 5 Desc;
select 'alter system disconnect session ' || '''' || sid || ',' || serial# || ''' immediate;' from v$session s where s.event='SQL*Net message from dblink' ;
select * from v$session s where s.event='SQL*Net message from dblink' ;
select p.* from v$session s, v$process p where p.addr=s.paddr and s.sid=945 ;
select * from SYS.Dba_Jobs_Running ;
alter system disconnect session '8,57999' immediate ; alter system kill session 'SID,Serial#';
SELECT DECODE(v.request,0,'Holder: ','Waiter: ') || v.sid sess, v.inst_id, v.id1, v.id2, v.lmode, v.request, v.type, s.sid,s.serial#,s.last_call_et,s.action,s.event FROM gv$lock v, gv$session s WHERE (v.id1, v.id2, v.type) IN ( SELECT v1.id1, v1.id2, v1.type FROM gv$lock v1 WHERE v1.request>0) and s.inst_id=v.inst_id and s.sid=v.sid ORDER BY v.id1, v.request;
//message should not Streams AQ: waiting for messages in the queue
select SID,Serial#, client_identifier,action,event,user#,username,status from gv$session where client_identifier like '%41899%' and status='ACTIVE'
select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
//machine wise
select sid,serial#,last_call_et,action,event,program,machine,osuser,username from v$session s where s.machine like 'SML%' ;
select sid,serial#,last_call_et,action,event,program,machine,osuser,username from v$session s where status='ACTIVE' and type='USER'
//deadlock
select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
select (select username || ' - ' || osuser from v$session where sid=a.sid) blocker, a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial, ' is blocking ', (select username || ' - ' || osuser from v$session where sid=b.sid) blockee, b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
select event,count(1) from v$session check for 'sql netmessage for dblink'
//which user is running what sql declare x number; begin for x in ( select username||'('||sid||','||serial#|| ') ospid = ' || process || ' program = ' || program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null order by last_call_et ) loop for y in ( select max(decode(piece,0,sql_text,null)) || max(decode(piece,1,sql_text,null)) || max(decode(piece,2,sql_text,null)) || max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4) loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time || ' ' || x.current_time|| ' last et = ' || x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) ); end if; end loop; end loop; end; /
//FIND LOCKING SESSIONS FOR A TABLE:
select object_id from dba_objects where LOWER(owner)='sml' and LOWER(object_name)='emp_master_site'; SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id and a.object_id=91198;
//gET LOCKING OBJECT
SELECT vh.sid locking_sid, vs.status status, vs.program program_holding, vw.sid waiter_sid, vsw.program program_waiting FROM v$lock vh, v$lock vw, v$session vs, v$session vsw WHERE (vh.id1, vh.id2) IN (SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request = 0 AND vw.lmode = 0 AND vh.sid = vs.sid AND vw.sid = vsw.sid;
//ref
https://oracleexamples.wordpress.com/2010/04/08/find-a-locking-session/
process wise session : ps aux --sort -rss | head -10
ps -ef | grep LOCAL
select a.sid,a.username,a.sql_id,a.last_call_et,a.action,a.event,a.program,a.machine,a.osuser,a.type,a.status from v$session a,v$process b where a.paddr=b.addr and b.spid like '%3716%'
select program,machine,osuser,username,s.module,to_char(logon_time,'DD-MON-YY HH24:MI:SS') || '|' || action || '|' || event || '|' || sid || '|' || serial#|| '|' || last_call_et from v$session s where status='ACTIVE' and type='USER' and (machine ) in (select machine from v$session s where status='ACTIVE' and type='USER' group by machine having count(1)>15) order by machine;
select a.sid||'|'|| a.serial#||'|'|| a.process from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_id and OBJECT_NAME=upper('&TABLE_NAME');
//get object id
select object_id from all_objects where LOWER(object_name) like '%RG%'
No comments:
Post a Comment