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'
/