Home » RDBMS Server » Server Administration » ORA-01562: failed to extend rollback segment (Oracle8i Release 8.1.5.0.0, windows XP)
ORA-01562: failed to extend rollback segment [message #420799] Wed, 02 September 2009 05:46 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Hi i am getting the error:

ORA-01562: failed to extend rollback segment number 15
ORA-01628: max # extents (121) reached for rollback segment RB14 -1562

Now after searching the above error i found that i have to use 'set transaction use rollback segment **** ' in the begining and after every commit. I need to ask that is it the appropaite way to do it? If yes then offcourse i should use the segment with maximum size so that it can accomate the maximum number of records but my question is how to check the size of the segment?
Re: ORA-01562: failed to extend rollback segment [message #420800 is a reply to message #420799] Wed, 02 September 2009 05:48 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
sorry i quote the wrong version, it is Oracle8i Release 8.1.7.4.1
Re: ORA-01562: failed to extend rollback segment [message #420803 is a reply to message #420799] Wed, 02 September 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01628: max # extents (%s) reached for rollback segment %s
 *Cause:  An attempt was made to extend a rollback segment that was
          already at the MAXEXTENTS value.
 *Action: If the value of the MAXEXTENTS storage parameter is less than
          the maximum allowed by the system, raise this value

Either your transaction is too long, either someone is blocking the rollback segment with a not ended transaction.

Regards
Michel
Re: ORA-01562: failed to extend rollback segment [message #420806 is a reply to message #420799] Wed, 02 September 2009 06:10 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:
either someone is blocking the rollback segment with a not ended transaction.


Can you explain this a bit please?
I have mulitple begin blocks in the procedure and everyone of them is running an insert. Is it what casuing a problem?
Re: ORA-01562: failed to extend rollback segment [message #420809 is a reply to message #420806] Wed, 02 September 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another session may have started a transaction and never committed or rolled back. Until it does it the rollback extent it used cannot be reused and so rollback segments has to increase until it reaches its maximum number of extents.

Regards
Michel
Re: ORA-01562: failed to extend rollback segment [message #420810 is a reply to message #420799] Wed, 02 September 2009 06:22 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:
Another session may have started a transaction...

no thats not the case.

Quote:
MAXEXTENTS storage parameter is less than the maximum allowed by the system, raise this value

how to raise the value?
SELECT MAX_EXTENTS FROM DBA_rollback_SEGS
is it what you are talking about?
Re: ORA-01562: failed to extend rollback segment [message #420812 is a reply to message #420810] Wed, 02 September 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no thats not the case.

It may have been days ago. Check.

Quote:
how to raise the value?

ALTER ROLLBACK SEGMENT

Regards
Michel

Re: ORA-01562: failed to extend rollback segment [message #420813 is a reply to message #420799] Wed, 02 September 2009 06:48 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
okay is there any way to check and then kill the session that might be holding the transaction, other than restarting the system?
Re: ORA-01562: failed to extend rollback segment [message #420817 is a reply to message #420813] Wed, 02 September 2009 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$transaction and v$session.
Check the definition in Reference book you will see how to find which ones started long ago on your rollback segments (using dba_segments) and how to join them.

Regards
Michel
Re: ORA-01562: failed to extend rollback segment [message #420826 is a reply to message #420799] Wed, 02 September 2009 09:21 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
okay i used the following query:
select
   substr(s.username,1,18) username,
   substr(s.program,1,15) program,
   decode(s.command,
       0,'No Command',
       1,'Create Table',
       2,'Insert',
       3,'Select',
       6,'Update',
       7,'Delete',
       9,'Create Index',
      15,'Alter Table',
      21,'Create View',
      23,'Validate Index',
      35,'Alter Database',
      39,'Create Tablespace',
      41,'Drop Tablespace',
      40,'Alter Tablespace',
      53,'Drop User',
      62,'Analyze Table',
      63,'Analyze Index',
    s.command||': Other') command, s.status, s.osuser
from v$session s, v$process p, v$transaction t, v$rollstat r, v$rollname n
where s.paddr = p.addr
and   s.taddr = t.addr (+)
and   t.xidusn = r.usn (+)
and   r.usn = n.usn (+)
order by 1

It gives me few active programs but all of them have the same name i.e oracle.exe how to find that particular job? Am i using the right query?
Re: ORA-01562: failed to extend rollback segment [message #420828 is a reply to message #420799] Wed, 02 September 2009 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It gives me few active programs but all of them have the same name i.e oracle.exe how to find that particular job?
To which session does "that" (above) refer?

Why do you always find things in the last place you look & why did you not start looking there in the 1st place?
Re: ORA-01562: failed to extend rollback segment [message #420833 is a reply to message #420799] Wed, 02 September 2009 09:40 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:
To which session does "that" (above) refer?

the one from which i am running that procedure
Re: ORA-01562: failed to extend rollback segment [message #420837 is a reply to message #420799] Wed, 02 September 2009 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>the one from which i am running that procedure
To which session does "that" (above) refer?

SQL> select distinct sid from v$mystat;

       SID
----------
       159
Re: ORA-01562: failed to extend rollback segment [message #420853 is a reply to message #420799] Wed, 02 September 2009 11:20 Go to previous message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Quote:
SQL> select distinct sid from v$mystat;

21
Previous Topic: displaying of running processes
Next Topic: ORA-12518: TNS:listener could not hand off client connection
Goto Forum:
  


Current Time: Mon Jul 01 00:31:30 CDT 2024