Home » RDBMS Server » Server Administration » Kill back end session & Time taken to kill session (merged) (10.2.0.2.0 ,windows)
Kill back end session & Time taken to kill session (merged) [message #430541] Tue, 10 November 2009 23:35 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Hi ,

How to kill a back end session.The query was running for a long time. so have decided
to kill the session. (in windows platform)

Can i follow these steps to kill back end session ?

alter system kill session 'sid,serial#' immediate;

or kill the session using task manager ??


Thanks
Re: Kill back end session [message #430548 is a reply to message #430541] Wed, 11 November 2009 00:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hitman11 wrote on Tue, 10 November 2009 23:35

Hi ,

How to kill a back end session.The query was running for a long time. so have decided
to kill the session. (in windows platform)

Can i follow these steps to kill back end session ?

alter system kill session 'sid,serial#' immediate;

or kill the session using task manager ??


Thanks


If you know what are 'sid,serial#' and from where you can get then follow...

Do what ever you want and...first read the manual and decide yourself.Nothing say about that....Thats your database( i hope testing ).

Sriram.

[Updated on: Wed, 11 November 2009 00:15]

Report message to a moderator

Re: Kill back end session [message #430558 is a reply to message #430548] Wed, 11 November 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please stop quoting the whole post each time. It is obvious you answer to OP as there is ONLY OP's post.

@Hitman11,

As you are a beginner, use SQL*Plus and plain command to start and learn how Oracle works. When you will be confortable with these commands then you can use GUI.

Regards
Michel
Re: Kill back end session [message #430783 is a reply to message #430558] Thu, 12 November 2009 05:49 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
When a session is killed using 'ALTER SYSTEM' it takes long time to release the locks
and rollback the transactions.How would one know the lock is released and
transaction is rolled back?

Is there any query to view that ?

Thanks
Re: Kill back end session [message #430823 is a reply to message #430783] Thu, 12 November 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$transaction, used_ublk column gives the number of blocks that Oracle still has to roll back.

Regards
Michel
Re: Kill back end session [message #430826 is a reply to message #430823] Thu, 12 November 2009 07:38 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Thanks michel.

how to find out in terms of time ? (transaction rollback time )

Regards,
Re: Kill back end session [message #430829 is a reply to message #430826] Thu, 12 November 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take 2 snapshots at 10 seconds interval then you know how many blocks it has rolled back during this period and then infer the time to end (assuming the rollback speed is constant).

Regards
Michel
Re: Kill back end session [message #430831 is a reply to message #430829] Thu, 12 November 2009 07:46 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Thanks michel .


Regards,
Time taken to kill session [message #439258 is a reply to message #430541] Fri, 15 January 2010 07:32 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Hi all,

I killed a session using 'ALTER SYSTEM KILL SESSION 'SID,SERIAL#'.

The session has been killed but the status was showing KILLED.Can somebody tell ,how to find how long it will take to completely kill the session ?


Thanks
Re: Time taken to kill session [message #439260 is a reply to message #439258] Fri, 15 January 2010 07:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member


Kill session


sriram Smile

[Updated on: Fri, 15 January 2010 08:24]

Report message to a moderator

Re: Kill back end session [message #439267 is a reply to message #430831] Fri, 15 January 2010 08:26 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
As discussed in this thread,

"v$transaction, used_ublk column gives the number of blocks that Oracle still has to roll back."

But i see used_ublk column increasing from 64 to 3000 in sqlplus .What does it mean?

Does it mean it has 3000 undo blocks to rollback ?

Please explain.


Regards,


Re: Kill back end session [message #439273 is a reply to message #439267] Fri, 15 January 2010 08:51 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

used_ublk -> Number of undo blocks used

- Babu
Re: Kill back end session [message #439275 is a reply to message #439273] Fri, 15 January 2010 08:56 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Thanks babu.

i used 'select used_ublk from v$transaction'; its showing no rows selected.

But the status in v$session view is still showing KILLED.

Please explain

Regards,
Re: Kill back end session [message #439277 is a reply to message #439275] Fri, 15 January 2010 09:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's possible that your session is waiting for a remote session to finish/rollback, and that the remote session is just sitting there twiddling it's thumbs.
Re: Kill back end session [message #439283 is a reply to message #439275] Fri, 15 January 2010 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 24913
Registered: January 2009
Senior Member
>But the status in v$session view is still showing KILLED.
In some/many/most cases Oracle session remains in the DB while OS process remains on the system.
Terminate OS process will break the logjam.
Re: Kill back end session [message #439316 is a reply to message #439283] Fri, 15 January 2010 10:37 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Thanks.

Which is the beast practice to kill a session ?

Current Status.
I see the session status as SNIPED from v$session.
and used_ublk column is null.

Sometimes i see null values under used_ublk column and
sometimes i see values in the same column.

Please explain .

Regards,
Re: Kill back end session [message #439324 is a reply to message #439316] Fri, 15 January 2010 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Current Status.
I see the session status as SNIPED from v$session.
and used_ublk column is null.

The session has been killed by Oracle (due to profile limit exceeded), Oracle already does the cleaning, so no more undo block used.

Quote:
Which is the beast practice to kill a session ?

"alter system kill session" unless you have more urgent/important needs than a clean stop.

Regards
Michel
Re: Kill back end session [message #439326 is a reply to message #439324] Fri, 15 January 2010 11:25 Go to previous message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Thanks

Correction

"Which is the beast practice to kill a session ?"

Which is the best practice to kill a session ?


Regards,
Previous Topic: Oracle 11g Installation
Next Topic: DB Time Zone change (merged)
Goto Forum:
  


Current Time: Fri Sep 30 12:28:54 CDT 2016

Total time taken to generate the page: 0.14564 seconds