-
Hi All,
I got a problem in dropping a user. Let me explain in detail.
I have to drop a user 'TELCO'.
I tried to drop the user by connecting to Sysytem user.
>DROP USER TELCO CASCADE;
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
then i querried in V$SESSION for sid,serial#,status and tried to killl the session.
>ALTER SYSTEM KILL SESSION '16,4558';
ERROR at line 1:
ORA-00031: session marked for kill
This error is because of the process is in UN-INTERRAPTIBLE mode(rollback or network operation...etc)
My Question is, if it is in process why its status in V$SESSION is showing as KILLED?
And How to Drop this user?
Please reply back me ASAP.
thank you ,
esreddy.
-
Hi,
If the session, has submitted a job and Oracle is Processing a request submitted by the
client process then it cannot kill that session immediately. So, it marks the session as
Killed and as-soon-as the processing is over, it removes the information about the
user from V$SESSION view. Until, the record is removed, it means that Oracle has not
killed the users' session completely.
Vijay.
-
This has happened often to me too. Try to kill the session/process on OS level.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Vijay what u said is correct, but in the V$SESSION Table , STATUS of user should not be KILLED, till the process is completed, atleat it should be INACTIVE. Am i correct?
Originally posted by oravijay
Hi,
If the session, has submitted a job and Oracle is Processing a request submitted by the
client process then it cannot kill that session immediately. So, it marks the session as
Killed and as-soon-as the processing is over, it removes the information about the
user from V$SESSION view. Until, the record is removed, it means that Oracle has not
killed the users' session completely.
Vijay.
-
Hi,
As soon as you get the confirmation as 'The session is marked for Killed' it means
that the user cannot execute subsequent statements(apart from the statements which
had been already submitted and is currently getting executed). If the user
try's to execute any other statement he will receive the error "Your
session has been Killed".
So I think that the
message what Oracle gives is correct.
Vijay.
-
If you want to get rid of this session right away, go to OS and issue the ORAKILL for NT or KILL -9 for Unix to get rid of them, but you need to know the OS number of this session
-
Originally posted by mike73
If you want to get rid of this session right away, go to OS and issue the ORAKILL for NT or KILL -9 for Unix to get rid of them, but you need to know the OS number of this session
Run
Code:
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr
/
If you get something like:
SID USERNAME OSUSER FG_PID BG_PID
---- --------------- ---------- --------- ---------
10 JULIAN JULIAN 1965:6969 1234
run in unix as the oracle user
kill -9 1234
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|