OraFAQ Forum: Server Administration » Kill back end session & Time taken to kill session (merged)

Home » RDBMS Server » Server Administration » Kill back end session & Time taken to kill session (merged) (10.2.0.2.0 ,windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Kill back end session & Time taken to kill session (merged) [message #430541]

Tue, 10 November 2009 23:35

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

Report message to a moderator

Re: Kill back end session [message #430548 is a reply to message #430541]

Wed, 11 November 2009 00:11

ramoradba
Messages: 2457
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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Kill back end session [message #430783 is a reply to message #430558]

Thu, 12 November 2009 05:49

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

Report message to a moderator

Re: Kill back end session [message #430823 is a reply to message #430783]

Thu, 12 November 2009 07:28

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Regards
Michel

Report message to a moderator

Re: Kill back end session [message #430826 is a reply to message #430823]

Thu, 12 November 2009 07:38

Hitman11
Messages: 94
Registered: October 2009
Location: norway

Member

Thanks michel.

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

Regards,

Report message to a moderator

Re: Kill back end session [message #430829 is a reply to message #430826]

Thu, 12 November 2009 07:40

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Kill back end session [message #430831 is a reply to message #430829]

Thu, 12 November 2009 07:46

Hitman11
Messages: 94
Registered: October 2009
Location: norway

Member

Thanks michel .

Regards,

Report message to a moderator

Time taken to kill session [message #439258 is a reply to message #430541]

Fri, 15 January 2010 07:32

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

Report message to a moderator

Re: Time taken to kill session [message #439260 is a reply to message #439258]

Fri, 15 January 2010 07:39

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Kill session

sriram Smile

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

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,

Report message to a moderator

Re: Kill back end session [message #439273 is a reply to message #439267]

Fri, 15 January 2010 08:51

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

used_ublk -> Number of undo blocks used

- Babu

Report message to a moderator

Re: Kill back end session [message #439275 is a reply to message #439273]

Fri, 15 January 2010 08:56

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,

Report message to a moderator

Re: Kill back end session [message #439277 is a reply to message #439275]

Fri, 15 January 2010 09:08

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.

Report message to a moderator

Re: Kill back end session [message #439283 is a reply to message #439275]

Fri, 15 January 2010 09:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Kill back end session [message #439316 is a reply to message #439283]

Fri, 15 January 2010 10:37

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,

Report message to a moderator

Re: Kill back end session [message #439324 is a reply to message #439316]

Fri, 15 January 2010 11:17

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Kill back end session [message #439326 is a reply to message #439324]

Fri, 15 January 2010 11:25

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,

Report message to a moderator

Previous Topic:	Oracle 11g Installation
Next Topic:	DB Time Zone change (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 11:43:38 CST 2024