[askdba] Re: Malfunctioning Session

  • From: "Subodh Deshpande" <subodh_deshpande@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 28 Oct 2004 00:07:25 -0500

hi sanjay,

i usually kill the session by alter system kill session ('sid,serial#')
and identify them by username, machine, teminal, status, program etc,

you may use sql_address, sql_hash_value..
already pepling has given you more information on recent sqls etc.

just think whether following query help you=20

select sid, serial#,USERNAME,status, command,SERVER,
SCHEMA#, SCHEMANAME, OSUSER,    PROCESS, MACHINE,
TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE,
PREV_SQL_ADDR, PREV_HASH_VALUE,  MODULE,   MODULE_HASH
from v$session
----- Original Message -----
From: <sanjay.khangarot@xxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Subject: [askdba] Re: Malfunctioning Session
Date: Thu, 28 Oct 2004 09:10:59 +0530

>=20
>=20
> Well suppose in a real system environ I have one application who authoris=
es=3D
>  the users on the basis of the rights provided in the tables ( not oracle=
=3D
>  users) but generic user and in the application we need to check the priv=
s=3D
>  to them.
> The user are allowed to generate the run time query.. and can execute=3D
>  those.
> As I have stated it may be an infinite loop generated by a user, or a bad=
=3D
>  behaving sql which affecting the performace. I need to pick that session=
=3D
>  and need to kill it..
> I was just trying to simulate an environ and wanted to figure out how can=
 I=3D
>  pick the session and kill it? It was just one trial to understand better=
=3D
>  the dynamic performace views and to correlate the info available there.=
=3D0D
> I had moved on the various options like=3D0D
> Picking of the sql from v$sql on the basis of CPU_TIME can figure out whi=
ch=3D
>  is the sql consuming max time, but from there how can I reach to the=3D
>  Session level.. still not able to resolve..
> If someone has any Idea ( Ganesh Already pointed out one), just let me=3D
>  know...
> till the time I will keep experimeting :)
>=20
> Regards=3D0D
>=20
> Sanjay=3D0D
>=20
> -----Original Message-----
> From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]On
> Behalf Of Irfan Khan
> Sent: Wednesday, October 27, 2004 5:45 PM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Re: Malfunctioning Session
>=20
>=20
> Hi Sanjay,
>=20
> Just a thought
>=20
> If u dont know who is the user and what machine, How will u know that =3D=
3D
> something has gone in infinite loop on the first hand?
>=20
> Regards
> Irfan Khan
>=20
> -----Original Message-----
> From: sanjay.khangarot@xxxxxxxxx [mailto:sanjay.khangarot@xxxxxxxxx]
> Sent: Wednesday, October 27, 2004 7:03 PM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Re: Malfunctioning Session
>=20
>=20
>=20
> Suppose I have 10 session connected to a database and out of the 10, in =
=3D3D
> one=3D3D3D
>  session some one wrote an infinite loop...=3D3D3D0D
> now I wanted to get that session ID to kill it.
> I do not know anything about the user or m/c etc..
>=20
>=20
>=20
>=20
> -----Original Message-----
> From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]On
> Behalf Of Ganesh Raja
> Sent: Wednesday, October 27, 2004 4:28 PM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Re: Malfunctioning Session
>=20
>=20
> Totally Lost here .. What are u trying to do ...=3D3D3D0D
>=20
>=20
> On Wed, 27 Oct 2004 16:03:53 +0530, sanjay.khangarot@xxxxxxxxx
> <sanjay.khangarot@xxxxxxxxx> wrote:
> >=3D3D3D0D
> > Hi,
> > It is for my understanding..
> >=3D3D3D0D
> > I was just simulating my system for some waits, and wanted to know =3D3D
> that=3D3D3D
>  how=3D3D3D3D
> >  can I figure out which session is malfunctioning?I have tried to go =
=3D3D
> with=3D3D3D
> =3D3D3D3D
> >  v$session_wait but it does not give me the info about the session =3D3D
> which=3D3D3D
>  is=3D3D3D3D
> >  consuming maximum resources, and I can not go for the v$sesstat as I =
=3D3D
> do=3D3D3D
> =3D3D3D3D
> >  not know what are the resource consumed by the session?
> >=3D3D3D0D
> > Confidentiality Notice=3D3D3D3D0D
> >=3D3D3D0D
> > The information contained in this electronic message and any =3D3D
> attachments=3D3D3D
>  to=3D3D3D3D
> >  this message are intended
> > for the exclusive use of the addressee(s) and may contain confidential =
=3D
> =3D3D
> or=3D3D3D
> =3D3D3D3D
> >  privileged information. If
> > you are not the intended recipient, please notify the sender at Wipro =
=3D3D
> or=3D3D3D
> =3D3D3D3D
> >  Mailadmin@xxxxxxxxx immediately
> > and destroy all copies of this message and any attachments.
> >=3D3D3D0D
> >
>=20
>=20
>=20
>=20
> Confidentiality Notice=3D3D3D0D
>=20
> The information contained in this electronic message and any attachments =
=3D
> =3D3D
> to=3D3D3D
>  this message are intended
> for the exclusive use of the addressee(s) and may contain confidential =
=3D3D
> or=3D3D3D
>  privileged information. If
> you are not the intended recipient, please notify the sender at Wipro =3D=
3D
> or=3D3D3D
>  Mailadmin@xxxxxxxxx immediately
> and destroy all copies of this message and any attachments.
>=20
>=20
>=20
>=20
> Confidentiality Notice=3D0D
>=20
> The information contained in this electronic message and any attachments =
to=3D
>  this message are intended
> for the exclusive use of the addressee(s) and may contain confidential or=
=3D
>  privileged information. If
> you are not the intended recipient, please notify the sender at Wipro or=
=3D
>  Mailadmin@xxxxxxxxx immediately
> and destroy all copies of this message and any attachments.
>=20
>=20



Good Luck..Subodh Deshpande

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Experience Is Knowledge
Wisdom Is Philosophy
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D







--=20
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as=
p?SRC=3Dlycos10


Other related posts: