| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Serial# changes when rolling back
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
>
-- 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).Received on Tue Dec 11 2001 - 04:11:29 CST
 |
 |