1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where username='SCOTT';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions from any server except any connections coming from the database server?
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where machine='production';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions except any background processes and my current session;
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select
sid, serial# from v$session
where
username is not null
AND
username not in
(select
username from v$session
where
sid =
(select distinct sid from v$mystat));
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
Generate a SQL output that you can copy paste in the SQL Editor to kill all the Oracle Sessions (except the background processes)
set pagesize 100
select
'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;' as Kill_the_following_connections
from
(select
Sid,
Serial#
from
v$session
where
username is not null
and
username not in ('SYS','SYSTEM','SYSMAN','DBSNMP'));
To kill the sessions from LINUX (except the background processes and local database connections)
ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
The above has worked for me. Although sometimes I see few stale connections that I can kill later with any of the above methods but mostly, the above works.
Below is my testing results. When time permits, I will add more on my steps but for now, lets just say it is pretty much self explanatory.
Let's see how many users are connected to the database.
set linesize 200
set username format a20
col machine format a20
set pagesize 60
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username is not null
-- and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
DBSNMP production 175 42 25213 13467
SYSMAN production 156 1796 25237 1234
SYSMAN production 164 41 25247 1234
DBSNMP production 165 135 25281 13467
SYS production 141 25733 25375 25374
SCOTT production 182 45691 25437 25436
SCOTT AAA\1-11816-LAPXP 142 13808 25490 5352:5396
SYSTEM AAA\1-11816-LAPXP 163 245 25512 1724:5616
SYSMAN production 162 37192 26511 1234
SYSMAN production 154 64670 26516 1234
SYSMAN production 181 34355 26520 1234
SYSTEM production 170 33536 26551 1234
SYS production 158 24367 26682 26681
13 rows selected.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 25213 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25237 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25247 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25281 1 0 01:04 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 25490 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25512 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26511 1 0 01:10 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 26516 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26520 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26551 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
As you can see some of the SPID are associated with DBSNMP and SYSMAN schemas. At this point, you can skip all those users by manually killing each session minus any DBSNMP/SYSMAN connections
OR
use the following method to kill all the connections which are not local to the DB Server. This includes all DBSNMP and SYSMAN connections as well.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }'
25213
25237
25247
25281
25490
25512
26511
26516
26551
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
At this point, I check the outside (local=no) connections, and I see the following.
SQL> SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 27400 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27402 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27438 1 0 01:22 ? 00:00:00 oraclePrime (LOCAL=NO)
If my assumption is right, DBSNMP and SYSMAN connections connected back after they realized that connection was lost. Lets check it out
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
-- and
-- a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and
-- a.machine like 'DR%'
-- and
-- a.username='SCOTT'
and
a.username is not null
--and
-- a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
SYS production 158 24367 26682 26681
DBSNMP production 182 45779 29655 13467
SYSMAN production 165 145 29657 1234
SYSMAN production 145 17657 29668 1234
As you can see, every outside connection is dead except the connections which are internal and connections from DBSNMP and SYSMAN. Althought more testing is needed, my guess is dbconsole will invoke its connections after they shutdown ungracefully. If you are still in doubt, then restarting dbconsole won't hurt.
The above method of killing Oracle connections from outside of SQL Plus has always worked for me. If you have any other idea or have a better way, please share with us.
Thanks,
--Moid Muhammad
Other Notes:
Note-1
Before killing sessions, if possible stop new sessions from connecting.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION; To kill a session, the syntax is
Alter system kill session "SID,SERIAL#" immediate;
Note-2
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
From the Oracle Database Administrator's Guide:
Note-3Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active...Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state
How to find the current session's ID (the session you are connected)
select sid, serial# from v$session where audsid = sys_context('userenv','sessionid');
Or, if you're only interested in the sid, not in serial#:
select distinct sid from v$mystat;
Note, the selected sid has nothing to do with the System Identifier.
If dbms_support is installed, the current sid can also be found with its mysid function:
The package is not installed by default. ..../rdbms/admin/dbmssupp.sql will install it.
select dbms_support.mysid from dual;
Note-4
Try trigger on logon
Insted of trying disconnect users you should not allow them to connect.
There is and example of such trigger.
CREATE OR REPLACE TRIGGER rds_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
IF (to_number(to_char(sysdate,'HH24'))< 6) and (to_number(to_char(sysdate,'HH24')) >18) THEN
RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');
END IF;
END;
/
Note-5
The below query can be used to identify those sessions which are inactive from last 3 minutes.
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
To kill the above sessions which are INACTIVE from last 3 minutes, use the following:
DECLARE
CURSOR c1 IS
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
Note-6
from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1419803982336
then just make the query be:
select ..
from v$session
where sid <> ( select sid from v$mystat where rownum=1)
or -- just
SQL> startup force;
:) would have the same effect.
Note-7
Showsql.sql from AskTom site. Just copy and paste the below in /tmp as showsql.sql script. It will come in handy.
Also, notice the last column is showing time in seconds.
column username format a15 word_wrapped
column module format a35 word_wrapped
column action format a15 word_wrapped
column client_info format a35 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
set linesize 200
set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
pid number;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' command = ' || command ||
' 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, sid, paddr
from v$session
where status = 'ACTIVE'
and sid <> ( select sid from v$mystat where rownum = 1 )
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
select max(spid) into pid from v$process where addr = x.paddr;
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username || ' dedicated server=' || pid );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p( y.sql_text );
end loop;
end loop;
end;
/
set feedback on
Note-8
Note-9
Note-10
No comments:
Post a Comment