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).
Received on Tue Dec 11 2001 - 18:58:00 CST
