scripts

blwt

set lines 400 pages 600
select sid,/*+RULE*/  DECODE(request,0,'HOLDER: ','Waiter: ')||sid sess,inst_id,id1,id2,lmode,request,type from gv$lock
where (id1,id2,type) IN (select /*+RULE*/  id1,id2,type from gv$lock where request>0) order by id1, request
/

ap

select * from table(dbms_xplan.display_cursor('&sqlid',&child))
/

ev

set lines 400 pages 400
select sid,serial#,sql_id,event,osuser,username,last_call_et/60,status,row_wait_obj# from v$session where event='&ev'
/

ex

select sql_id,child_number,executions,buffer_gets from v$sql where sql_id='&sqlid'
/

kill event

select 'Alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where  event= '&event';

kill sid

select 'Alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where  sid= &sid;

longops

set linesize 500;
set pagesize 400;
col OPNAME for a20;
col USERNAME for a18;
col TARGET for a23;
select INST_ID, SID, SERIAL#, OPNAME,TARGET, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS, USERNAME from
gv$session_longops where TIME_REMAINING > 1 order by TARGET;

sql text

select sql_text from v$sql where sql_id='&sqlid'
/

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top