| Database Not allowing further logins [message #63975] |
Wed, 01 December 2004 08:10  |
Shikha
Messages: 36
Registered: January 2002
|
Member |
|
|
hi,
I have a problem with my oracle server... I am the only user (with 2 sessions open - one active and another killed).
When I realized that the query I just fired is going to delete about 20000 records (from the table of about 60000 records) is taking more than 5 minutes, I tried to cancel it and resubmit it. But the cancel operation itself got hung.
I opened another session of the schema and tried to see explain plan for that delete query. I could see that it is doing a full scan since there was no index on the condition. Well, I tried now to kill the session which was trying to delete the rows... and it is still showing 'KILLED' since the last 2 hours. In fact now I have killed the process from the windows also. But the thread is still open in oracle server it seems. The worst part is, I am not able to open another session in oracle. And only the first session which was active is still active. But I am skeptic to fire any query. Last time also it hung up when I tried to check dba_locks... And in a matter of fact, the explain plan has also hung up now. Which I had to KILL and it is now INACTIVE.
What could be the problem. I had posted this message earlier, and thought I got the solution now... But this problem persists.
Please help,
(I may get dba help but it takes a lot of procedures and time)...
(I've DBA previleges in my userid)
Thanks,
Shikha
|
|
|
|
| Re: Database Not allowing further logins [message #63976 is a reply to message #63975] |
Wed, 01 December 2004 09:23  |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
I believe that your session is marked for kill, because it is rolling back all the work it was doing in order to perform delete. When rollback finishs, the session will be killed and resources will be freed.
Regarding you are not able to connect..i don't understand why you are facing that problem.
Best luck
|
|
|
|
| Re: Database Not allowing further logins [message #63982 is a reply to message #63976] |
Wed, 01 December 2004 20:58 |
Shikha
Messages: 36
Registered: January 2002
|
Member |
|
|
Hey,
is there a way I can know the process ID from OS level so that I can kill from the OS level itself?
if I do a ps -d I get scores of rows... Which one i would find appropriate to kill to kill my 'KILLED' oracle session?
thanks,
Shikha
|
|
|
|
| Re: Database Not allowing further logins [message #63987 is a reply to message #63982] |
Thu, 02 December 2004 03:52 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
i dont remember at all, but i think that some column from v$session should correspond to operating system process ID
and also you can check v$process
some join like:
select substr(b.username,1,15) "Username",substr(b.osuser,1,10) "OSUSER",substr(b.machine,1,10) "host",substr(b.program,1,15) "Program",b.status,b.lockwait,b.sid, b.serial#,to_char(logon_time,'DD-MON-YY HH:MI AM') "Login_time",b.client_infofrom v$session b, v$process c where c.spid = '&pid' and b.paddr = c.addr;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
