ORA-01562: failed to extend rollback segment [message #420799] |
Wed, 02 September 2009 05:46 |
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 #420806 is a reply to message #420799] |
Wed, 02 September 2009 06:10 |
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 #420810 is a reply to message #420799] |
Wed, 02 September 2009 06:22 |
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 #420826 is a reply to message #420799] |
Wed, 02 September 2009 09:21 |
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?
|
|
|
|
|
|
|