DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Urgent

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Exclamation

    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 ));

    */

  2. #2
    Join Date
    Feb 2001
    Posts
    295
    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.

  3. #3
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    Set maxextents to unlimited and turn Autoextend ON.

  4. #4
    Join Date
    Nov 2000
    Posts
    416
    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.


  5. #5
    Join Date
    Feb 2001
    Posts
    3
    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
  •  


Click Here to Expand Forum to Full Width