Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have Oracle 9.2

I view my running session with the query:

SELECT S92700.SYSUSR_VW."NAME", SID,SERIAL#, AUDSID
FROM v$session, S92700.SYSUSR_VW, S92700.SECUSR
WHERE S92700.SYSUSR_VW.SESSIONID = AUDSID AND S92700.SECUSR."NAME"=S92700.SYSUSR_VW."NAME" 

Result:

 NAME      SID      SERIAL#    AUDSID
 prot6      9        6503       78239

And i run the follow query to kill the session

alter system kill session '9,6503' immediate

SQL Command execution

Executed successfully in 0 s, 0 rows affected. Line 1, column 1

Execution finished after 0 s, 0 error(s) occurred.


The session still running

The same query in other schema is working

share|improve this question

migrated from stackoverflow.com Mar 7 '13 at 12:51

This question came from our site for professional and enthusiast programmers.

1  
What is your OS ? –  A.B.Cade Mar 7 '13 at 11:38
    
What does v$session.status say for the session? –  Phil Mar 7 '13 at 12:55

4 Answers 4

From a thread on AskTom, when a session is killed:

The session is killed but it is being cleaned up. When you kill a session -- outstanding work must still be cleaned up.

When you do DML on a table, Oracle may make the changes to the base table almost immediately, even if you don't commit. The other sessions don't see these changes yet thanks to multiversion read consistency but the actual physical block may already be overwritten with your uncommited work.

This is done because Oracle is optimized for commit. Most sessions should rarely rollback so Oracle anticipates by writing preemptively the changes. This is also one of the reasons that allows Oracle to have arbitrarily large transactions (limited only by the size of the undo tablespace). The changes are not delayed (much). This is also why you almost never wait for a commit in Oracle, even for multimillion-row DML.

One of the consequences is that rollback needs to undo the changes. The uncommited transaction is read back from the undo tablespace and each and every change is undone in reverse order. This can take longer than the initial DML for several reasons:

  • The rollback is not as well optimized, in particular multi-row operations can take a lot longer than regular DML.
  • Some changes are more expensive in reverse. For instance a delete which is a relatively cheap DML becomes an insert in reverse (which is more expensive).

This explains why rollback can take a lot of time. You have few means to accelerate the process. In any case you should not:

  • kill the rollback process
  • shutdown the database in the hope that this will terminate the session

If you shutdown the database normally (or transactional or immediate), Oracle will wait for the process to complete so that the database is left in a consistent state. If you shutdown ABORT, the database will be left in an inconsistent state (uncommited data written to disk) and Oracle will resume the rollback as soon as the database is restarted.

The rollback is handled by smon, I wouldn't try to kill it.


Some session, after being killed, will remain in the v$session view, even after all their changes have been entirely rolled back. This is often because Oracle is waiting for the client to return a relevant error message, such as:

ora-00028 your session has been killed

If the client itself has already left or the network communication has been severed, the killed session may stay indefinitely until you restart the instance. I don't think these sessions take resources.

share|improve this answer

Most of the time the session tries to tell the client something nasty happened. If the client is gone, this will never be acknowledged so the dbms will wait forever. When you kill a session, the connection between v$session and v$process is gone. The process of the killed session remains in v$process. You can find the OS pid for these processes by checking the processes that are not PQ slaves without a session. Kill those processes from the OS.

Oracle is very robust and if anything should be recovered, smon takes care of that, if needed. The state of the database does not depend on a client that gets killed. Would be a bit strange since this would mean that if a client crashes it would corrupt the rdbms... If a database gets - logically - corrupted it would mean that the client did not make use of the regular transaction mechanism.

The following SQL can be used to identify the processes that have no session tied to it:

select pid, spid, pname, program 
from v$process
where addr not in (select paddr from v$session)
and pname is null
and pid <> 1;

The regular session background processes can normally be killed. The special database processes ... many will automatically be restarted but better is to leave them running. Often those client processes have a wait event, something like

SQL*Net break/reset to client

share|improve this answer
    
Nice to know, I wouldn't have tried myself. Still, I'm not sure I understand your regular expression. The only string I can make it match is P0-90-9 which I'm quite sure is not any process name :) –  Vincent Malgrat Mar 7 '13 at 16:20
    
was trying to filter out P000 - P999; when not assigned to a session, you still don't want to kill PQ slaves. –  ik_zelf Mar 7 '13 at 16:28
    
you could filter on the background column maybe ? –  Vincent Malgrat Mar 7 '13 at 16:33
    
adjusted query to only show when pname is null => only show regular processes. –  ik_zelf Mar 7 '13 at 16:40

Have you tried ALTER SYSTEM DISCONNECT SESSION '9,6503' IMMEDIATE?

Accorting to the documentation, The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process.

share|improve this answer
    
it still running, nothing happens –  EmiDemi Mar 7 '13 at 10:22
1  
DO NOT kill it from the OS. It may be doing something important. –  Phil Mar 7 '13 at 12:55

To create job for killing session, please visit below link http://mujahidalishahani.blogspot.ae/2014/11/script-auto-job-to-kill-inactive.html

share|improve this answer
1  
This is a link only answer. Links disappear over time, making the post of little use. Please add some brief details to outline what is in the link. –  RLF 5 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.