If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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 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.

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.

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