70 lines
1.9 KiB
SQL
70 lines
1.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Session Details
|
|
* Compatibility : 10.1 and above
|
|
* Parameters : 1 - SID
|
|
* 2 - Instance Number
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
* 27-Mar-12 Vishal Gupta Included the session wait history
|
|
* 11-May-12 Vishal Gupta Change output layout. Instead of SELECT output
|
|
* now it display dbms_output lines.
|
|
*
|
|
*/
|
|
|
|
VARIABLE SID number ;
|
|
VARIABLE INST_ID number ;
|
|
|
|
BEGIN
|
|
:SID := &&1;
|
|
:INST_ID := NVL(&&2,1);
|
|
IF :INST_ID = '' OR :INST_ID IS NULL THEN
|
|
:INST_ID := 1;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
|
|
Prompt
|
|
Prompt ####### Kill/Disconnect Command ####################
|
|
|
|
COLUMN command HEADING "Disconnect Command" FORMAT a60
|
|
COLUMN command2 HEADING "Kill Command" FORMAT a60
|
|
|
|
BEGIN
|
|
FOR i IN (select 'alter system disconnect session ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; ' Command
|
|
, 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; ' Command2
|
|
from gv$session s
|
|
, gv$process p
|
|
where s.inst_id = p.inst_id (+)
|
|
AND s.PADDR = p.ADDR (+)
|
|
AND s.sid = :SID
|
|
AND s.inst_id = :INST_ID)
|
|
LOOP
|
|
DBMS_OUTPUT.PUT_LINE( i.command || chr(10)
|
|
|| i.command2 );
|
|
DBMS_OUTPUT.PUT_LINE( ' ' );
|
|
DBMS_OUTPUT.PUT_LINE( 'Killing session ...' );
|
|
execute immediate REPLACE(i.command2,';','');
|
|
DBMS_OUTPUT.PUT_LINE( 'Session killed.' );
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
BEGIN
|
|
:SID := NULL;
|
|
:INST_ID := NULL;
|
|
END;
|
|
/
|
|
|
|
UNDEFINE TOP_EVENT_COUNT
|
|
|
|
@@footer
|