intresting ...
- hemantchitale_at_charteredsemi.com wrote:
> One explanation I had heard for the combination of
> Sid+Serial# goes
> something like this ...
> say you identify a particular session to monitor ...
> you then decide to
> kill it.
> However, it could so happen that that user logged
> out between the time you
> decided to kill the session and the time you
> actually killed the session
> (even
> if you have scripted the kill command). When a user
> logs out, the Sid is
> available to the next user who logs in. Since
> another user might be
> holding
> the same Sid at the time you come around to kill the
> Sid, you might
> actually
> kill the wrong user-session. To avoid this, the
> next user, getting the
> same
> Sid gets a different Serial# and your kill must be
> on Sid+Serial# together.
>
> That still does not explain why pmon increments the
> Serial#, though.
>
> Hemant
>
>
>
> Deepak Thapliyal <deepakthapliyal_at_yahoo.com>
> 12/12/2001 06:26 AM
> Sent by: root_at_fatcity.com
>
> Please respond to ORACLE-L
>
>
>
> To: Multiple recipients of list
> ORACLE-L <ORACLE-L_at_fatcity.com>
>
> cc: (bcc: CHITALE Hemant
> Krishnarao/Prin DBA/CSM/ST Group)
>
> Subject: Re: Serial# changes when
> rolling back
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> yeah i did read this thread but still wonder why
> oracle needs to use serial# column at all. Does the
> SID not gaurentee uniqueness for a session ... damn
> the name says so atleast (session identifier)..
>
> or maybe they just coded it like that and make some
> use of the serial #
>
> Deepak
>
> --- hemantchitale_at_charteredsemi.com wrote:
> > Check this Oracle Note :
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Doc ID:
> >
> > Note:1020545.102
> >
> > Subject:
> >
> > ORA-00026: CANNOT KILL SESSION;
> >
> > SERIAL# KEEPS CHANGING
> >
> > Type:
> >
> > PROBLEM
> >
> > Status:
> >
> > PUBLISHED
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Content Type:
> >
> > TEXT/PLAIN
> >
> > Creation Date:
> >
> > 23-NOV-1999
> >
> > Last Revision Date:
> >
> > 24-AUG-2000
> >
> >
> >
> >
> >
> > Problem Description
> >
> > -------------------
> >
> >
> >
> > You have killed a process at
> >
> > the operating system level that
> >
> > was
> >
> > running a long-running
> >
> > transaction. Now, you are
> >
> > trying to issue
> >
> > the command:
> >
> >
> >
> > alter system kill session
> >
> > '<sid>, <serial#>';
> >
> >
> >
> > To kill the associated Oracle
> >
> > session, but you can't kill it.
> >
> > You
> >
> > may receive the following
> >
> > error:
> >
> >
> >
> > ORA-00026: missing or
> >
> > invalid session id
> >
> > Cause: The session ID
> >
> > string specified in the ALTER
> >
> > SYSTEM
> >
> > KILL SESSION
> >
> > command was invalid, or no
> >
> > string was
> >
> > specified.
> >
> > Action: Retry the
> >
> > command with a valid session
> >
> > ID.
> >
> >
> >
> > In v$session, you notice that
> >
> > the serial# for the session
> >
> > keeps
> >
> > changing. Also, pmon may be
> >
> > creating a trace file that
> >
> > keeps growing.
> >
> >
> >
> >
> >
> > Solution Description
> >
> > --------------------
> >
> >
> >
> > It is best to let pmon roll
> >
> > back the changes. If you
> >
> > shutdown at this
> >
> > point, this work will still
> >
> > have to be done at the next
> >
> > startup.
> >
> >
> >
> > pmon is rolling back changes
> >
> > and will let the session die
> >
> > when it
> >
> > is finished. To verify that
> >
> > work is being done, select
> >
> > used_urec
> >
> > from v$transaction. If the
> >
> > value for this column keeps
> >
> > going down,
> >
> > then work is being done. When
> >
> > used_urec reaches zero, then
> >
> > the
> >
> > rollback will be done, and the
> >
> > session will die.
> >
> >
> >
> > With Oracle8, you can list dead
> >
> > transactions by issuing the
> >
> > following query:
> >
> >
> >
> > select * from x$ktuxe
> >
> > where ktuxecfl='DEAD';
> >
> >
> >
> >
> >
> > Explanation
> >
> > -----------
> >
> >
> >
> > pmon has control of the session
> >
> > and is rolling back all of the
> >
> > work
> >
> > that has been done so far.
> >
> >
> >
> >
> >
> > Search Words
> >
> > ------------
> >
> >
> >
> > unable, runaway, status, serial
> >
> > number
> >
> > .
> >
> >
> >
> >
> >
> >
> >
> > My note :
> > You can tune the speed at which pmon cleans up the
> > session by tuning the
> > cleanup_rollback_entries init.ora parameter which
> > defaults to 20.
> >
> > Hemant
> >
> >
> >
> > Hallas John <John.Hallas_at_btcellnet.net>
> 11/12/2001
> > 04:55 PM
> > Sent by: root_at_fatcity.com
> >
> > Please respond to ORACLE-L
> >
> >
> >
> > To: Multiple recipients of list
> > ORACLE-L <ORACLE-L_at_fatcity.com>
> >
> > cc: (bcc: CHITALE Hemant
> > Krishnarao/Prin DBA/CSM/ST Group)
> >
> > Subject: Serial# changes when rolling
> > back
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Jared,Deepak
> > I did not see a reply on this. From a brief
> > experiment I don't see the
> > serial# changing when rolling back a transaction.
> >
> >
> > The code posted by Jared certainly works as the
> > number of blocks to
> > rollback reduces as the job nears completion.
> > If the serial# changes I would be interetsed to
> > understand why and to what
> > purpose
> >
> >
> > John
> > -----Original Message-----
> > Sent: 03 December 2001 17:55
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> >
> >
> > Hi Jared
> >
> >
> > why does the serial# have to change due to
> rollback?
> > lots of us would be curious for a brief expln ...
> >
> >
> > Thx
> > Deepak
> > --- Jared Still <jkstill_at_cybcon.com> wrote:
> > >
> > > The session is rolling back, you can't kill it.
> > >
> > > This is why the serial# is changing.
> > >
> > > The following query can be used to track its
> > > progress.
> > >
> > > select s.osuser
> > > ,s.username
> > > ,s.sid
> > > ,r.segment_name
> > > ,t.space
> > > ,t.recursive
> > > ,t.noundo
> > > ,t.used_ublk
> > > ,t.used_urec
> > > ,t.log_io
> > > ,t.phy_io
> > > ,substr(sa.sql_text,1,200) txt
> > > from v$session s,
> > > v$transaction t,
> > > dba_rollback_segs r,
> > > v$sqlarea sa
> > > where s.taddr=t.addr
> > > and t.xidusn=r.segment_id(+)
> > > and s.sql_address=sa.address(+);
> > >
> > > Jared
> > >
> > >
> > > On Sunday 02 December 2001 22:55, Tatireddy,
> > > Shrinivas (MED, Keane) wrote:
> > > > Hi lists,
> > > >
> > > > Solaris 2.7
> > > > oracle 8i
> > > >
> > > > I have a session "SYSTEM" doing import into a
> > > table. (logged into server
> > > > thru telnet from win 98 PC)
> > > >
> > > > Suddenly the power outage occurred to my PC.
> > > >
> > > > When I logged into the server thru telnet, I
> > found
> > > that the session is
> > > > active.
> > > > By mistake, I killed the process at o/s level.
> > > >
> > > > For somereasons,I tried to drop the table. But
> I
> > > failed to do it, as it
> > > > is locked by import process.
> > > >
> > > > I tried to kill the user "SYSTEM". But the
> > oracle
> > > is giving error that
> > > > there is not user with such sid and serial
> > number.
> > > >
> > > > The serial# number is often getting changed
> when
> > I
> > > query from v$session.
> > > >
> > > > Is there a way to kill this user, without
> > shutting
> > > down the database.
> > > >
> > > > And why different serial# number each time, I
> > > query v$SESSION.?
> > > >
> > > > Any clues?
> > > >
> > > > Thnx and Regards,
> > > >
> > > > Srinivas
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Jared Still
> > > INET: jkstill_at_cybcon.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> > FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> >
>
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Buy the perfect holiday gifts at Yahoo! Shopping.
> > http://shopping.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Deepak Thapliyal
> > INET: deepakthapliyal_at_yahoo.com
> >
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> >
> >
> >
> >
> >
>
> > This electronic message contains information from
> > the mmO2 plc Group
> > which may be privileged or confidential. The
> > information is intended to be
> > for the use of the individual(s) or entity named
> > above. If you are not the
> > intended recipient be aware that any disclosure,
> > copying, distribution or
> > use of the contents of this information is
> > prohibited. If you have received
> >
> > this electronic message in error, please notify us
> > by telephone or email
> > (to the numbers or address above) immediately.
> >
>
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: hemantchitale_at_charteredsemi.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for
> all of
> your unique holiday gifts! Buy at
> http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Deepak Thapliyal
> INET: deepakthapliyal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: hemantchitale_at_charteredsemi.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at
http://shopping.yahoo.com
or bid at
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: deepakthapliyal_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 11 2001 - 19:57:20 CST