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).
Received on Tue Dec 11 2001 - 16:35:26 CST
