killing then dropping the user
Phil, June 12, 2003 - 5:58 pm UTC
tom
I am using this command to kill sessions for a user. I am then wanting to Drop that user.
as below ...
begin
for i in (select sid,serial# from v$session where username = 'UNITTEST' ) loop
execute immediate 'alter system kill session ' ||''''|| i.sid ||','|| i.serial# ||''' immediate';
end loop;
end;
drop user UNITTEST cascade;
however I am unable to drop the user because I get an ORA-01940. When I check the v$session I see the user a status of killed. At what point can i drop this user ?.... the session seems to stay there until I go to the client (of UNITTEST) and perform an action and get told my session has been killed. Then when I look at the v$session the user has gone. I can then drop that user !
Seems odd but I am sure there is an explanation !
regards
June 12, 2003 - 8:03 pm UTC
we are waiting for the client application to do something in the database so we
can report back to it "i'm sorry, you are dead, you were killed", rather then
just report back "ora-3113 eof on communication channel" which most people would
interpret as "bug"
killing then dropping the user
Phil, June 12, 2003 - 8:41 pm UTC
thanks
can i get around this i.e. kill the sessions and then drop the user in one script ?
regards
June 12, 2003 - 8:48 pm UTC
not really, seems to be an "unusual", "not standard" case here. you would have to kill at the OS level, something I would not suggest personally. It is an "odd" situation to say the least.
Kamal Kishore, June 12, 2003 - 8:57 pm UTC
Hi Tom,
In your earlier reply, you said:
<quote>
we are waiting for the client application to do something in the database so we
can report back to it "i'm sorry, you are dead, you were killed"
</quote>
What if the client then somehow vanishes. The client machine crashes or as happens with few Operating Systems, the client gets a blue screen and presses the reset button. In this case, will Oracle wait forever for a response from client just to tell it that it has been killed (given that now the client has actually already been killed by its own actions)?
Thanks,
June 13, 2003 - 7:32 am UTC
yes. unless tcp ip is kind enough to say "pipe broken"
killing then dropping the user
phil, June 12, 2003 - 9:50 pm UTC
thanks,
reasoning for wanting to do this is that, the database is rebuilt everynight on the test servers, in order for automatic unit tests to run against the application and database.
i need a clean database, for my rebuild (no tables, no indexes, no connections, no users, no roles etc )... as long as there are sessions can I do this ?
regards... sorry if this all sounds very basic !
June 13, 2003 - 7:49 am UTC
shutdown abort;
restore from production (test your backup)
startup;
done.
or
shutdown abort;
startup enabling restricted session
drop away
rebuild
turn off restricted session
(i like the "just restore from prod's backups" as it buys you that extra knowledge that "yes, in fact not only do our backups work but we also know exactly how to use them")
Not the best practice but may help
Jim, June 12, 2003 - 9:58 pm UTC
Phil,
This is not an ideal solution but given that your
databases are test machines.
I am pretty sure I have a script somewhere that
mimics the user hitting "CNTRL-C" and may do what you want.
I only used it for testing that ungraceful session ends
were hadnled correctly by Oracle and the application
but it may do what you want in that it should kill the session and let you drop the user immediately
I'll see if I can dig it up
No go - Sorry
Jim, June 12, 2003 - 11:28 pm UTC
Sorry Phil,
Just gave my idea a quick test and unfortunately
it behaves like killing the session as described above
Regards
Jim
Kill Session
A reader, September 30, 2003 - 2:16 pm UTC
Tom:
1) what is the difference between "disconnect session" and "kill session" ? The manuals are not very clear I think...
2) This scenario happens to me:
o alter system kill session
o the session is marked for kill
o no rows selected from v$transaction
o drop user above cascade informs it cannot be droped because user is still logged on -- though killed
My last resort was issuing a kill -9 in unix against the server process.
What is the rationale here ? I am using 9ir2 on Solaris.
September 30, 2003 - 2:38 pm UTC
1) disconnect is primarily for TAF (failing over), so you can fail a connection over from one instance to another. you can have it do that after the transaction ends so as to not lose work. It is "nicer" then kill in that respect
2) that is the way KILL SESSION works. the session is dead, killed, resources released. However the session remains -- this is so the client process which is still connected can report gracefully to the end user "your session was killed, goodbye". What happened was that user had a session opened (maybe a sqlplus session). It was just sitting there -- and would continue to sit there until it went to the database -- then the session would actually "disappear" -- after the client got the message.
Otherwise, the client would just (did in your case since you kill -9'd it) recieve "ora-3113 eof on communication channel". People would report that as "a bug"
continued
A reader, September 30, 2003 - 2:45 pm UTC
ok, but
1) If it was killed (resources released, etc), why couldn't I drop the user cascade (the msg was: user was still connected) ?
2) Why should I kill -9 it ? Isn't there another "nicer" way ?
September 30, 2003 - 4:03 pm UTC
1) because the user was was still connected! when you kill a session, that releases the resources. the session stays and stays killed until the CLIENT picks up the fact that it was killed.
2) you could have exited the client application this user was running, that would have done it.
Killing a session
Sandy, October 15, 2003 - 3:05 pm UTC
We are using Oracle 92.
In our application, we drop and create Oracle users to support multiple dataset manipulation (each dataset is loaded into separate Oracle user). Our app users can have multiple datasets in multiple Oracle users.
when application user closes one dataset we "exp" data.
when the user tries to load it again, we drop and create Oracle user, do "imp" of "exp" data, then recreate indexes and seq.
Some times we are facing this session issue and the scripts are not able to drop the user and create the Oracle user
Can you suggest a single script to kill the sessions of a Oracle user and drop the user?
thanks,
Sandy
October 16, 2003 - 9:51 am UTC
nope, not really.
you can kill the user session -- but it'll still be there (just killed).
suggestion: don't drop the user, just clean the schema out if you want.
Dropping connected user.
Robert, November 14, 2003 - 6:53 am UTC
Hi Tom
We are using oracle proxy authentication connection pooling. Part of our application has the ability to drop the user if he is no longer required.
However, we are getting an error where pseudo connections are sometimes being left open by the connection pool and so we can't drop the user. The code is in the connection pool to close the proxy connection after sql execution and it works when we test it normally so we reckon it only happens when something strange happens (we cannot replicate it).
Is there anyway we can ensure closure of these connections at all times seeing as we cannot force the dropping of connected users?
Thanks.
Robert.
November 14, 2003 - 9:17 am UTC
well, to be dropping users on a regular basis like that seems "strange" -- but you have a bigger issue here -- you need to figure out why and where you are keeping a connection going. sounds like an un-handled exception in your java code -- you grab a connect and then "something bad happens" and you've just orphaned that connection (it is lost forever, until you bounce the app server).
maybe you need a "when others" like handler in your java code, to make sure the connections are not getting "leaked" like that.
A reader, April 20, 2004 - 9:28 pm UTC
One of our dbas has suggested using this script below to find sessions which are running for more than 2 hours, if yes then those sessions will be killed(oracle & then os level). i disagree using this process.
can you please look at this script and provide your feedback if it's gonna work as expected
SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC
April 21, 2004 - 7:29 pm UTC
why would they not use resource profiles?
but yes, that'll id any CALL that has been going on for 2ish hours -- if the joins are right -- last_call_et can be used for that.
A reader, April 21, 2004 - 10:35 pm UTC
Thats what my suggestion was. got a example from your site, works perfect.
Thanks
killed sessions not removed, but processes yes
Jan, May 25, 2004 - 2:50 am UTC
I killed some sessions 1,2 - 7 days ago, but they are still in the v$session table (marked as killed). The following query:
---
SELECT spid
FROM v$process
WHERE addr IN (SELECT paddr
FROM v$session
WHERE status='KILLED')
--
returns no rows. All the values in V$SESSION.PADDR for the killed session have the same value. Is it the way to remove the kiled sessions without restarting database? Version 9.2.0.1 - on Linux Redhat 9
Thank you, Jan
May 25, 2004 - 7:17 am UTC
they will be there until the client attempts a database action -- then the client will be told, "sorry, you have been killed on purpose"
the alternative is -- client attempts a database action - then client gets "ora-3113 end of file on communication channel" and tars are opened.
Kill all user sessions except me
Teymur, July 30, 2004 - 4:24 am UTC
Dear Tom.
I looged as sys. I want to kill all sessions except me.
1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null
4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;
SQL> /
alter system kill session '9, 188'
alter system kill session '10, 2183'
alter system kill session '11, 2546'
alter system kill session '12, 5159'
alter system kill session '14, 50'
declare
*
ERROR at line 1:
ORA-00027: cannot kill current session
ORA-06512: at line 11
I think when exception occurs it must terminate work of subblock not full for..loop as explained in Pl/SQL User Guide.
Thanks in advance.
July 30, 2004 - 8:00 am UTC
as explained in the manual -- you need an exception block.
begin
blah blah blah
exception
when .....
end;
you need an exception handler, not just a block.
Bill, July 30, 2004 - 10:17 am UTC
Maybe what Oracle needs is an enhansement to the kill session command that would force an immediate kill on a session. This would satisfy the people who need it. For example,
alter system kill session '9, 188' immediate;
This would not generate tars to oracle since the people that would be putting in the tar is the one doing the killing.
July 30, 2004 - 5:01 pm UTC
that is the purpose of disconnect session (relatively new feature):
ops$tkyte@ORA9IR2> @showsql
USERNAME SID_SERIAL STATUS MODULE ACTION
--------------- --------------- ---------- --------------- ---------------
CLIENT_INFO LAST_CALL_ET
--------------- ------------
OPS$TKYTE '9,33' ACTIVE SQL*Plus
0
OPS$TKYTE '10,93' INACTIVE SQL*Plus
4
2 rows selected.
ops$tkyte@ORA9IR2> alter system disconnect session '10,93' immediate;
System altered.
ops$tkyte@ORA9IR2> @showsql
USERNAME SID_SERIAL STATUS MODULE ACTION
--------------- --------------- ---------- --------------- ---------------
CLIENT_INFO LAST_CALL_ET
--------------- ------------
OPS$TKYTE '9,33' ACTIVE SQL*Plus
0
1 row selected.
it works with "networked" connections (since oracle would not be the process owner in a bequeath)
the disconnected session gets:
ops$tkyte@ORA9IR2> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
TO: Bill from Liverpool, NY USA
Marcio, July 30, 2004 - 10:39 am UTC
There is already an IMMEDIATE clause accord manual (9ir2):
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2054627 <code>
KILL SESSION Clause
...
Whether or not the session has an ongoing transaction, Oracle does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been killed.
See Also:
"Killing a Session: Example"
IMMEDIATE
Specify IMMEDIATE to instruct Oracle to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.You may be want to through DISCONNECT SESSION clause on the same page.
Bill, July 30, 2004 - 11:44 am UTC
Immediate couldn't be used, but the same idea applies. The dba should have the ability to kill a session and not have to wait until the user tries to do something and is told that they have been killed. All immediate does is to rollback the transaction, it still waits for the user. An option is needed that will chop the session immediatly without having to unix kill the process. Maybe
alter system kill session 'sid,serial#' force;
What ever you call it, there are times when you just don't want to wait for the user to try to access the database.
July 30, 2004 - 5:31 pm UTC
disconnect does that as long as "oracle" owns the dedicated server (so it can kill it)
Kill all user sessions except me
Teymur, August 01, 2004 - 11:32 am UTC
Tom, thanks for reply.
But I tried it as below:
begin
execute immediate sql_stmt;
exception
when others
...
end;
But result is the same. It interrupts the main block.
Thanks in advance.
August 01, 2004 - 11:56 am UTC
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.
kill session privilege
Sean, August 04, 2004 - 4:40 pm UTC
Hi Tom,
We want to grant lead developer kill session with grant option privilege. Right now we have to grant him dba privilege in order to let him do that, which we dont like. Is there a way to do that.
Thanks so much for your help.
Sean
August 05, 2004 - 8:46 am UTC
grant them execute on this procedure with the grant option........
use the *concept here*. any priv you want can be wrapped in a procedure like this, you control the procedure.
@showsql.sql
Marcio, August 30, 2004 - 1:11 pm UTC
Could you publish your latest showsql.sql please?
Thanks,
August 30, 2004 - 1:43 pm UTC
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a15 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
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
Meaning of last_call_et in v$session
Sean, October 28, 2004 - 10:37 am UTC
Hi Tom,
What is meaning of last_call_et of v$session? The last time session is active that many seconds ago?
Thanks so much for your help.
October 28, 2004 - 1:49 pm UTC
if session active -- how long the call it made has been working
if the session is inactive -- how long it has been idle.
Killed session
Yogesh, November 16, 2004 - 4:52 am UTC
One of the user executed some process at OS level, after some time he realised that there was some problem with loop and he killed that process using ^C.
The process was still shown as active in oracle v$ession view. So I killed that process at oracle level.
Now that process is listed with status 'KILLED'. It is still inserting rows in the table mentioned in the script. It's almost 24 hours now, process is still executing. Is there any way to kill that process (apart from shutdown abort)?
Will shutdown immediate work in this scenario?
November 16, 2004 - 6:41 am UTC
shutdown abort would work (and is very safe and if you are going to "bounce" probably the fastest).
kill -9 on the dedicated server should work as well. pmon will clean up after it.
Kill -9
Yogesh, November 16, 2004 - 7:22 am UTC
^C didn't killed the OS process. The process was getting listed in ps -ef, without the script name. When I checked spid from v$process it was matching with those processes.
I killed them from OS and it worked.
session without machine name and os user.
Sean, June 03, 2005 - 2:04 pm UTC
Hi Tom,
Sometimes we found session in v$session without machine name and osuser. Here is the one:
1* select count(*) from v$session where machine is null and osuser is null
SQL> /
COUNT(*)
----------
1
Normally, these are the sessions which are running long time and using a lot of cpu. We can see the query and know the schema name. How could session have no machine name and osuser?
9204. Solaris 9
Thanks so much for your help.
Sean
June 03, 2005 - 5:11 pm UTC
is the type background -- indicating an oracle background process.
The session without machine and os user
Sean, June 03, 2005 - 2:36 pm UTC
The developer told me that this is the session created by job queue.
Sean
June 03, 2005 - 5:15 pm UTC
yup, background.
Killing a session
Pavan, June 04, 2005 - 6:58 am UTC
Will the same pid be used or assigned to other process immediately after a process has been complteted? If so, How Shud i confim at the point of execution about "KIllin the same process i want to"
June 04, 2005 - 8:35 am UTC
pid's are assigned by the operating system, it would be highly unlikely to get it assigned "immediately".
if IDLE_TIME parameter not set...
Craig, July 12, 2005 - 2:09 pm UTC
Tom,
In note 1061189.6, it's stated that:
<quote>
...If you have many users accessing a database another option is to not use the IDLE_TIME parameter in the user profile. Without an IDLE_TIME parameter set, a session will not go into the 'sniped' status. The need for Oracle to give the 'terminated session' message to the user is removed and the alter system, kill session command will kill the session and release the session, allowing another user to occupy this session...
</quote>
This leads me to believe that if the user has an active or idle session that an "alter system kill session 'sid,pid'" will kill and cleanup. However, I've not found that to be the case.
Also, there's a script provided in the document that will kill the shadow process of a sniped or killed session that will otherwise hang around until that user returns to try and access the session they had left behind. What are your thoughts on it?
Thanks and best regards!
July 13, 2005 - 10:39 am UTC
I'll have to review that note when I get a chance later, that doesn't look right to me.
kill shadow process script works...
Craig, July 14, 2005 - 12:44 pm UTC
Tom,
I worked with Oracle Support for a solution to our sniped session problem. They instructed me to use a script provided by note 96170.1 on MetaLink, the syntax of which follows:
#!/bin/sh
tmpfile=/tmp/tmp.$$
sqlplus system/system_password <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile
I've tested in development and this seems to do the job nicely.
July 14, 2005 - 1:04 pm UTC
Oh, no doubts on that, but the QUOTED TEXT above is *wrong*
killed sessions...
Craig, August 05, 2005 - 5:14 pm UTC
Tom,
I tried to modify this script to remove the "KILLED" sessions as well, but it doens't seem to work as I expected. Is there a difference between a killed and sniped session on Oracle/Solaris, or am I not modifying the script correctly?
Thanks!
August 05, 2005 - 5:52 pm UTC
"this script", which one? remove killed sessions from what?
A lots usefull answer
Suvendu, August 06, 2005 - 10:18 am UTC
Thanks a lot for your quick response.
I got clear from your answer.
Sorry, I didn't enable the parallel option in the session. As I told you the target table is a partitioned one, do I need to judge any more setting for it? And I can use NOLOGGING hint for performance.
There are 147 partitioned in the target table and it has to insert 18974536 numbers of rows.
Sorry for every one
comments. Ok, its exclusive for me
:-)
Once again thanking you
Suvendu
August 06, 2005 - 10:28 am UTC
nologging is not a HINT
nologging is a segment attribute (eg: if you stuff the word nologging into your insert as select, you haven't done anything)
Testing the script to kill sniped sessions ....
felipe, September 27, 2005 - 9:55 am UTC
Hi Tom,
I´ve tested the following script in dedicated mode and it seems to be ok :
#!/bin/sh
tmpfile=/tmp/tmp.$$
sqlplus system/system_password <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile
But using shared server mode It kills all the other users. Is there any way to kill only one session in shared server mode ?
Thank you,
Felipe
September 27, 2005 - 11:42 am UTC
other than leaving the shared server connection be? no. you are killing a process and in shared server (shared PROCESS server) you are killing a shared resource
Disconnect Current Session through PL/SQL
Nishith Pandey, December 18, 2005 - 6:20 am UTC
Hi Tom
I want to disconnect the current session from a PL/SQL procedure in some condition. Is it possible to logout/disconnect/kill the current session from a Procedure?
Please help me. Awaiting your valuable reply.
December 18, 2005 - 10:44 am UTC
No, PLSQL doesn't have any way to do that - as plsql is running IN the database, there is no way to terminate the database connection from the inside. This is a
"client thing"
Nishith Pandey, December 19, 2005 - 2:44 am UTC
Hi Tom,
Thanks for the prompt response. But please tell me the alternative. I elaborate you my problem:
We have over 25 offices running the same business processes and each having a seperate database server. In our head-office, we have created the snapshots for all the office-servers to get the combined report of all offices. In each office, we have deputed an EDP incharge to implement the software developed in the head office.
But, sometimes the incharges don't implemented the updated copy of form/report. So we want to enforce them to do it.
To control it, I thought of an idea. We can initialize CLIENT_INFO with Version Number and MODULE with form/report name through DBMS_APPLICATION_INFO package whenever the users login through the form/report. And then, (through some database trigger) compare the same with a table containing the list of forms/reports and their latest version. If the version doesn't match, we disconnect the session. I thought to use the LOGON ON DATABASE trigger, but we can not read CLIENT_INFO, MODULE that time, as it initializes after logon is done.
Can we have some background JOB to terminate the session, and set it run a little late through LOGON trigger so that the CLIENT_INFO and MODULE could be initialized?
I know this may not be the correct strategy, but for now, We have to go for some feasible solution.
Please suggest us the best possible in our case.
Thanks again for your quick followups !
December 19, 2005 - 7:30 am UTC
please read the other page you and I are having this discussion on - I've told you how to implement this over there and it has nothing to do with killing sessions.
Combined View
Nishith Pandey, December 20, 2005 - 6:15 am UTC
Hi Tom
Thanks for that great idea given in other page!!
Kill and Wait
Parikshit Paul, February 12, 2006 - 10:37 pm UTC
Hi Tom,
I killed a session (which performed delete operation), but the session was marked for kill.So i thought the session must be rolling back entries.But the session seemed to hang forever.On querying the v$session_wait for the sid it showed:
Event :resmgr:waiting in check2
State :WAITING
What is the meaning of this wait event ?
Is it safe kill the process from os level?
February 13, 2006 - 8:11 am UTC
killing a session will not remove it. killing a session removes locks, rolls back work - and then waits for the client application to try and use it again so that we can tell that client (nicely), so sorry - your session was killed, goodbye.
Sessions remains active
Praful, March 19, 2006 - 5:29 am UTC
Hi Tom
At my place there are two DB on two diff m/c A & B
From B , one session is creating on DB A
once the user logged in into DB B using application, the session created on DB A has to go , but the problems is here only
The session created on DB A must have to go and this one process consumes 6% CPU. There are at a time more than 5-6 such procesess
If we check the rows processed in V$sqlarea then it slowly increased by 1-2 two value
These are all ACTIVE sessions
Why this sessions are thre ??
Why don't these goes off
March 19, 2006 - 7:04 am UTC
This doesn't make sense. Are you using dblinks?
Sessions remains active
Praful, March 19, 2006 - 9:42 pm UTC
Yes I am using a DBlinks to connect to other DB
rgds
Praful
March 20, 2006 - 7:07 am UTC
so, dblinks stay open until you
a) close them (alter session - documented)
b) exit the original session.
if you want to close them, you may do so.
how do i activate the killed session
keertana, April 18, 2006 - 2:47 am UTC
Sir,
i have killed a session. now I want to activate the session is there any way.
thanks and regards
April 18, 2006 - 9:35 am UTC
reincarnation?
it is quite dead, gone. No frankenstein sessions here - not sure what you mean by "active" in this context.
session status shows inactive
A reader, July 26, 2007 - 11:43 am UTC
Hello Tom,
I am querying v$seesion to see the status of sessions and its showing me status inactive for those sessions too which is performing transactions and active. Can you give your openion about this.
Thanks
July 27, 2007 - 9:04 am UTC
when you looked, that session WAS NOT executing a sql statement.
do this
a) log into sqlplus
b) update a row (you have a transaction)
c) go to another window and query v$session
your session in A will be inactive - it isn't DOING anything right then, you have a session, you have a transaction but you are currently INACTIVE.
Thanks a lot Tom...You are the best
A reader, July 27, 2007 - 10:00 am UTC
Sessions Hang in Oracle 11g
Emad Kehail, April 06, 2009 - 4:45 am UTC
Hello Tom,
We have recently upgraded our Oracle 9i database to Oracle 11g 11.0.7.
Our applications are implemented in Forms 6i and Reports 6i. We also use ASP.Net and some ASP pages.
We have noticed Forms and Reports clients are hanging in the database after the upgrade. I have tested this from my laptop. I have opened 4 forms and closed them normally. Everything is fine, sessions disappeared from the DB. However, I have opened another 4 sessions and I had my laptop goes to sleep mode without disconnecting the sessions normally. After 30 minutes, I have checked the DB and the sessions are still alive and active.
The DCD is enabled at the server side and it is set for 5 minutes.
SQLNET.EXPIRE_TIME = 5
We are afraid we are facing the Bug 6918493
The PGA sine in Oracle 9i were 450 MB. Now in Oracle 11g it is 2 GB and it is not enough because the sessions are hanging.
Kindly asking for your help.
April 13, 2009 - 10:03 am UTC
strange definition of a "hang", in fact, a wrong use of the term "hang"
that bug says the dedicated server would self deadlock itself, causing a failure, causing a session to cease working. You are not experiencing that at all, the symptoms are quite different for you. Your sessions are fine, it just appears that dead client detection is functioning with the old client library against the newer database release.
This is an obvious "we need to work with support to identify the issue" issue, please utilize support.
Behavior of Stored Procedure After Session Killed
Dylan, July 06, 2009 - 1:01 am UTC
I was playing around with killing a session that was executing a stored procedure. I was seeing if I could get away with using named exception for ORA-00028 to set a status to failed if the session was killed. The results were a little unintuitive to me. I thought I'd ask for some clarification of whats going on in the background just because I'm curious to know.
Here's a quick example case (Using Oracle XE and SQLDeveloper as a client):
drop table t;
create table t (col number,status varchar2(10));
CREATE OR REPLACE PROCEDURE p
AS
KILLED exception ;
pragma exception_init(KILLED, -28);
BEGIN
--
execute immediate 'TRUNCATE TABLE T';
--
FOR x IN 1..5
LOOP
BEGIN
INSERT INTO t(col,status)
VALUES(x,'STARTED');
COMMIT;
dbms_lock.sleep(5);
UPDATE t
SET status = 'COMPLETED'
WHERE col = x;
COMMIT;
--
EXCEPTION WHEN KILLED THEN
NULL;
END;
END LOOP;
--
END p;
When I run this procedure, and then kill the executing session, I see the "unexpected" behavior.
begin
p;
end;
/
-- session killed¿reconnect
select *
FROM t
order by col;
COL STATUS
---------------------- ----------
1 COMPLETED
2 COMPLETED
3 STARTED
4 STARTED
5 STARTED
5 rows selected
As expected, the name exception is catching the ORA-00028 because no error is returned to my client. What I didn't expect was to see 'STARTED' records for the remaining iterations of the loop (killed it fairly quickly). I thought they would be 'COMPLETED' or that some further exception would have been raised.
Just to see what would happen, I played with some variations on it.
CREATE OR REPLACE PROCEDURE p
AS
KILLED exception ;
pragma exception_init(KILLED, -28);
lv_status1 VARCHAR2(10) := 'STARTED';
lv_status2 varchar2(10) := 'COMPLETED';
BEGIN
--
execute immediate 'TRUNCATE TABLE T';
--
FOR x IN 1..5
LOOP
BEGIN
INSERT INTO t(col,status)
VALUES(x,lv_status1);
--COMMIT;
--
UPDATE t
SET status = lv_status2
WHERE col = x;
COMMIT;
dbms_lock.sleep(20);
--
EXCEPTION WHEN KILLED THEN
lv_status1 := 'KILLED1';
lv_status2 := 'KILELD2';
END;
END LOOP;
--
END p;
COL STATUS
---------------------- ----------
1 COMPLETED
2 KILLED1
3 KILLED1
4 KILLED1
5 KILLED1
So the exception handler is actually able to do something. When I try to put an update in the exception handler to set the status to failed, that passes an ORA-00028 back up. Same thing happens if I comment out the commit after the insert.
Last major variation on the procedure....
CREATE OR REPLACE PROCEDURE p
AS
KILLED exception ;
pragma exception_init(KILLED, -28);
BEGIN
--
execute immediate 'TRUNCATE TABLE T';
--
FOR x IN 1..5
LOOP
BEGIN
INSERT INTO t(col,status)
VALUES(x,'STARTED');
COMMIT;
EXCEPTION WHEN KILLED THEN
null;
END;
BEGIN
UPDATE t
SET status = 'COMPLETED'
WHERE col = x;
COMMIT;
EXCEPTION WHEN KILLED THEN
null;
END;
BEGIN
dbms_lock.sleep(20);
EXCEPTION WHEN KILLED THEN
null;
END;
END LOOP;
--
END p;
Still getting 5 records, where the status is 'STARTED' for all records inserted after the session was killed. But also, now it seems that it's executing the DBMS_LOCK for each iteration as well.
So it seems like as long as you handle the ORA-00028, you can keep on processing as long as you don't update (and probably delete). Is this the case? Obviously you can still get things to execute after the session is killed, so would there be any way to do the update? Or is handling a killed session just bad juju?
A reader, November 15, 2009 - 3:37 pm UTC
Hi tom;
Eventhough I read oracle manuels, I still have doubts about kill session and disconnect session.
Whats the main difference bewteen kill session and disconnect session?
Which one is more powerful?
November 15, 2009 - 3:47 pm UTC
more powerful is in the eye of the beholder, so I'll let you decide which you think is more powerful.
one kills a session (releases locks and other resources) but keeps the session object so that the application gets a graceful "ora-28, you have been KILLED, goodbye" message from the server.
disconnecting is not so friendly - it just zaps the dedicated server, the client doesn't get a nice message.
sessions
A reader, November 16, 2009 - 9:37 pm UTC
What about privileges?
adderek, November 17, 2009 - 5:56 pm UTC
Hi Tom,
I think that there are some incomplete information in your answer (although the answer is correct):
1. The user needs to have correct priveleges - AFAIK there is no way of granting permition to kill sessions of specific user only. A common problem is when developer wants to kill his own session but has no priveleges.
It seems that PL/SQL code running as priveleged user is the common (only?) way to fix this.
2. The KILL often takes a while to kill.
3. The KILL IMMEDIATE often takes a while to kill.
4. If a session is doing something huge - KILL IMMEDIATE might wait until current operation is finished (which might take years...).
5. Many tools (ex. Oracle SQL Developer) often fails to break execute operation (clicking some button shows no result... at least for several minutes).
I think that you should mention all of the above problems. Especially the delay.
Regards
November 23, 2009 - 2:28 pm UTC
1) when you have specific occasions whereby you want to extend the security model in some way that the database does not already implement, we have given you definer rights procedure to implement whatever you can dream up.
So yes, you would have a schema with ALTER SYSTEM, CREATE PROCEDURE, CREATE SESSION and select on v_$session.
This schema would create a procedure that would accept two numbers as input.
It would verify that the numbers represent the sid,serial# of a session owned by the current user.
it would then (using to_char() with an explicit format on the numbers to ward off any possibility of sql injection) issue the alter system command.
You would then lock this account and/or revoke create session from it.
Might as well revoke create procedure too.
2 & 3) ok? It might take a long time to roll back that which it is killing. The longer it has been going forward, the longer it will take to roll back and release the resources.
4) that is sort of obvious, I didn't really think it necessitated mentioning.
5) I don't know what you are trying to say with that one.
Application reaching Max Open cursors
Sudhir, November 18, 2009 - 3:34 pm UTC
Hi Tom,
I really appreciate for giving us information on open cursor issues. We have an application
which needs to go to production but in the test environment we are easily reaching the maximum open
cursors. It haven't given us ORA-ERROR but we are sure that it will happen soon. Our application
has code something like this
Ps1 = null
Ps2 = null
Ps3 = null
Rs1 = null
Rs2 = null
Rs3 = null
try{
Ps1 = con.prepareStatment(query1)
Rs1 = Ps1.executeQuery()
while(Rs1.next())
{
Ps2=con.prepareStatement(query2)
Rs2=Ps2.executeQuery()
Ps3=con.prepareStatement(query3)
Rs3=Ps3.executeQuery()
}
}
catch(){}
finally{
Ps1,Ps2,Ps3,Rs1,Rs2,Rs3,con CLOSE
}
Is something wrong in this code.Sorry if I miss-spelled something wrong.
November 23, 2009 - 3:10 pm UTC
why would that hit max open cursors?
it is ugly inefficient code - anytime I see sql in a loop like that - I know it could be done in a single SQL statement with less to no procedural code - but why are you afraid of max open cursors in this one? They seem to be closed.
A reader, November 21, 2009 - 6:32 pm UTC
Hi Tom;
Thank you very much for explaining the diffrence between kill session and disconnect session.
I understand that, kill session kills the process from Oracle and disconnect session kills the dedicated server process
from operating system same as (kill -9 spid)
Sometimes, after kill operation I get "session marked for kill", in that case I kill spid from operating system.
I guess disconnect session is similar to this case.
Correct me if I am wrong.
Thanks again
November 23, 2009 - 4:10 pm UTC
disconnect session would be the proper way to get rid of a session and the dedicated server ( realizing the application will get a nasty ora-3113 error if they ever try to use the database again ), you should not kill processes from the OS level.
Application reaching Max Open cursors
Sudhir, November 25, 2009 - 11:25 am UTC
Thank you for response. I really appreciate it. If the statements and result sets are used in the WHILE LOOP will it not treat them as separate/different resultset/prepare statements each time it iterates.This is just my assumption I am not sure whether it is right. In our case this while loop will go for around 200-300 times. If I happen to close those resultset/preparedstatements in the while loop will it cause any performance or other issues.
November 27, 2009 - 3:52 pm UTC
test it, create yourself a test program and through into it a "select * from v$open_cursor where sid = (select sid from v$mystat where rownum=1)" and print out the apparently open sql through each iteration.
using the same prepared statement over and over would have the effect of closing whatever it was associated with before.
it would only be if you had code that declared the prepared statement inside of the loop in a manner such that each iteration had it's own "instance" (each ITERATION) of the prepared statement - then you would have a leak.
A reader, November 28, 2009 - 11:23 pm UTC
Thanks Tom;
You advised me not to kill session from O.S.
What is the disadvatage of killing a dodgey session from Operating system(kill -9)?
November 29, 2009 - 9:01 am UTC
it is not something you do, that is all. If you wanted the session to be gone, disconnect it, if you just want the resources released, kill it.
Stored Procedure still lock even id session was killed
Ariel, April 09, 2010 - 4:10 am UTC
Stored Procedure still on lock even session was killed
Good day tom,
I open a session SID=37, Serial#=977 and run a stored procedure extracting data from various remote databases using dblinks. I terminate the stored procedure and then killed the session. The session status marked as KILLED.
As what you said in the previous comments "killing a session will not remove it. killing a session removes locks, rolls back work"
The problem is when I tried to modify the same stored procedure and compile it, It says "ORA-04021: timeout occured while waiting to lock object OWNER.STORED_PROCEDURE"
Does this mean that even if a session was killed, it did not release the stored procedure and still locked?
What is the work around for this without restarting the database?
Many Thanks,
April 13, 2010 - 8:44 am UTC
A reader, May 09, 2010 - 5:04 pm UTC
How you define which session run and how long time ?
May 10, 2010 - 6:40 pm UTC
I have no clue what you mean
After Killing Session , still locks are are not geting released
Mukesh, October 13, 2010 - 4:48 am UTC
Hi tom ,
we have one table , while updating we have selected one record to update from user A, the same record other user B is using , now i killed the session 'A' & in V$session 'A ' is marked as KILLED , but still the locks are not going some time . As you said the more time it will take to got forward to do the transcation the same it will take to go back for rollback or in their previous condition . In my only one record in updating then also some time (not always ) it is not getting realeasd.
Can you please advice how to check & what to do for this case
October 13, 2010 - 8:20 am UTC
show me the work you did to verify that the session you have killed still has that specific lock (and that it is not some other session)
Killing session
lalu, October 21, 2010 - 4:33 am UTC
Hi Tom,
I have come across similar situation.
A killed session was holding the lock and I found (Checked in TOAD) the session to be shown as KILLED.
After doing a kill -9 SPID, it released the lock.
Thanks.
How to Remove Killed Sessions from Database
Ashish Chamoli, November 16, 2010 - 12:19 am UTC
Hi,
i had created a database job which is used to kill the inactive sessions of a particular program from v$sessions, but problem is killed sessions visible in database and when no of session cross the limit defined in init.ora database dosn't allow application user or oracle user to login in database.
Suggession required to remove Killed sessions from database.
Ashish
Who killed my session
Ralph V, March 02, 2011 - 4:03 am UTC
Hi Tom:
Is there a way to determine who killed a session? I was trying to write a trigger to flag down who issued a kill statement and I can't find a suitable triggering event.
Thank you
March 02, 2011 - 7:43 am UTC
there is not, you could AUDIT for the use of the ALTER SYSTEM privilege globally.
Session Not getting Killed.
Snehasish Das, March 31, 2011 - 1:24 am UTC
Hi Tom,
I ran a select query from PLSQL Dev, but then the query went to IO Slave wait. I tried to stop the running query from the tool but it didnt help and hence i had to close the tool. The query was still running, i asked the dba to kill the query with alter system kill session , the query was marked for kill, but never killed.No matter how much we tried the query never gets killed. Any perticular reason for it. How do we kill such sessions.
Thanks and Regards,
Snehasish Das
April 12, 2011 - 10:02 am UTC
disconnect the session.
The session is likely killed, but still there because it is waiting for the tool (which doesn't exist anymore) to get the ora-28, your session has been killed message.
use alter session disconnect.
Get rid of a "Killed session"
Vinay, August 12, 2014 - 2:26 pm UTC
Hi Tom,
We have this session which I killed some time ago (alter system kill session). The status is still KILLED and it is "waiting for SQL*Net Message from DB Link". It is not doing any "work" - nothing in v$transaction.
But the problem is, the statement which was originally fired in this problem session was a CTAS - "create table t as select * from xyz@dblink", for example. So now, if we try creating a table with the same name (t), it waits on the previous session and basically never progresses.
Is there any way out at all, apart from killing the process at the OS level or bouncing the DB?
Thanks,
Vinay