-
I have this error:
Failure to extend rollback segment 12 because of 1628 condition
Error 1562 trapped in 2PC on transaction 12.23.548327. Cleaning up.
FULL status of rollback segment 12 cleared.
Error stack returned to user:
ORA-01562: failed to extend rollback segment number 12
ORA-01628: max # extents (121) reached for rollback segment R01_06
I don't want to add more datafile to this rollback R01_06. I want to find exactly which application/transaction cause the grief. I run the following scripts but still confuse what I have to do. Please advice.
-- Usage of ROLLBACK segment(s) by current active transactions
SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
/*
-- Finding any blocking transactions
SELECT s.sid, s.serial#, t.start_time, t.xidusn, s.username
FROM v$session s, v$transaction t, v$rollstat r
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND (( r.curext = t.start_uext-1) OR
(( r.curext = r.extents-1) AND
t.start_uext = 0 ));
*/
-
Have you set the OPTIMAL storage parameter on rollback segment? In that case, you can increase MAXEXTENTS, Oracle will deallocate automatically unused extents.
At least, your db will keep working until you find the application that caused the error.
Adriano.
-
Set maxextents to unlimited and turn Autoextend ON.
-
But How can I found the name and location of the transaction that cause the grief.
I want to tune the program or pl/sql that everyday do this to our rollback segment.
-
You may want to look at V$SQL_AREA view. That shows all the SQL statements executed and the statistics associated with them. You may find all the updates, deletes and inserts here.
HTH,
Ravi Kulkarni
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|