intresting ... --- [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > 12/12/2001 06:26 AM > Sent by: [EMAIL PROTECTED] > > Please respond to ORACLE-L > > > > To: Multiple recipients of list > ORACLE-L <[EMAIL PROTECTED]> > > 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 > > --- [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > 11/12/2001 > > 04:55 PM > > Sent by: [EMAIL PROTECTED] > > > > Please respond to ORACLE-L > > > > > > > > To: Multiple recipients of list > > ORACLE-L <[EMAIL PROTECTED]> > > > > 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
