Re: Serial# changes when rolling back

Past experience with this problem seems to indicate that you might be mistaken in that. Though the serial # may increment, I've never seen another session take the SID until the session holding it is completely gone.

This should be fairly easy to test if you want to bother. Start a sufficiently large transaction that it will take some time to rollback, kill it, and login with a new session and check v$session.

Jared

On Tuesday 11 December 2001 16:30, 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: 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).