?
Solved

ora-01562

Posted on 2004-08-03
8
Medium Priority
?
1,016 Views
Last Modified: 2008-01-09
Hi Experts!!!, I have a big problem, I was processing a pl/sql and then shows me error ora-01562.

Please I need your help soon.

Karen
0
Comment
Question by:karencita78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Accepted Solution

by:
bvanderveen earned 200 total points
ID: 11710650
Rollback segment problem.

When you create a transaction (via insert, update, delete) in oracle, the transaction is recorded in a rollback segment until the transaction is committed or rolled back.  If there are a lot of open transactions, and not enough rollback segments (or the segment is small), the additional transactions may "wrap" around and overwrite the old transaction, generating this error.  

Solutions:
   1. Examine your PL/SQL closely.  
   2. If you are processing a large number of records, you may need incremental commits or to have a larger rollback segment.

This query will show users with open transactions, and the rollback segment they are using:

select r.name,
  l.pid,
  p.spid,
  nvl(p.username,'no transaction') username,
  p.terminal
from v$lock l, v$process p, v$rollname r
where l.pid = p.pid(+)
   and trunc(l.id1(+)/65536) = r.usn
   and l.type(+) = 'TX'
   and l.lmode(+) = 6
order by r.name

Hard to provide any more help without code or additional error messages - hope this helps.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 11712124
ORA-01562: failed to extend rollback segment number string
Cause: Failure occurred when trying to extend rollback segment.
Action: This is normally followed by another error message that caused the failure. You may take the rollback segment offline to perform maintenance. Use SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE SEGMENT_ID=string (where string is the segment number from the message) to determine the rollback segment name. Then use the ALTER ROLLBACK SEGMENT OFFLINE command to take the rollback segment offline.
0
 

Expert Comment

by:quellcoder
ID: 11715189
For future reference, I've often used the ORA.HLP file to check what each of the error messages mean (number, text, cause, action), as a starting point to troubleshooting.

If you have a version of Oracle prior to 8.1.6, you'll have the ORA.HLP file available among your CDs (or already in your Oracle directory). It was shipped in previous releases, but apparently is no longer available from Oracle.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 7

Expert Comment

by:bvanderveen
ID: 11716836
You might also have your DBA check the rollback tablespace, and make sure it is large enough.
0
 
LVL 7

Expert Comment

by:BobMc
ID: 11718039
You have probably ran out of rollback tablespace or rollback extents

You can either increase either / both of these, or manually shrink other rollback segments down to make room.

We really need to next few lines from your error stack for a definitive answer.

HTH
Bob
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 11737274
Solution ========  The solution would be to drop and create larger rollback segments that would be  capable of handling the larger amount of redo generated.   1. From SQL*Plus or SQL Worksheet, connect internal.  2. Take the rollback segment offline.     ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;  3. Check the current status of the rollback segment.     SELECT STATUS FROM DBA_ROLLBACK_SEGS    WHERE SEGMENT_NAME = '<rollback_segment>';          If the query returns 'OFFLINE', go to step 4.         If it returns 'ONLINE 'or 'PARTLY AVAILABLE', it means that there         are still pending transaction entries in the rollback.  You         must wait until the corresponding transactions are either         committed or rolled back.  Repeat the above query again until         the status becomes 'OFFLINE'.          An 'INVALID' status implies that the rollback has already         been dropped.         A 'NEEDS RECOVERY' status means that there are problems with         the rollback. See bulletin 107693.969 for more information on how to         deal with a rollback segment that needs recovery.  4. Drop the rollback segment.          DROP ROLLBACK SEGMENT <rollback_segment>;          If you receive the message 'Statement processed', the rollback         has been dropped and can now be recreated.  5. Recreate the rollback segment.          CREATE ROLLBACK SEGMENT <rollback_segment>         TABLESPACE tablespace         STORAGE ( <storage parameters> )   6. Bring the rollback segment online.    ALTER ROLLBACK SEGMENT <rollback_segment> ONLINE;  Sample  The following statement creates a rollback segment and provides storage parameter values:   CREATE ROLLBACK SEGMENT RBS1      STORAGE ( INITIAL  10K  NEXT 10K                MINEXTENTS 2  MAXEXTENTS 25                 OPTIMAL 50K );   Oracle allocates space for the rollback segment based on the STORAGE parameter  values as follows:   The MINEXTENTS value is 2, so Oracle allocates 2 extents for the rollback  segment creation.   The INITIAL value is 10K, so the first extent's size is 10 kilobytes.   The NEXT value is 10K, so the second extent's size is 10 kilobytes.   If the rollback data exceeds the first two extents, Oracle allocates a third extent.                                                                                                                                               The PCTINCREASE value for rollback segments is always 0, so the third and subsequent  extents are the same size as the second extent, 10 kilobytes.   The MAXEXTENTS value is 25, so Oracle can allocate as many as 25 extents for the  rollback segment.   The OPTIMAL value is 50K, so Oracle deallocates extents if the rollback segment exceeds  50 kilobytes. Oracle deallocates only extents that contain data for transactions that  are no longer active.  
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11737311
Karen,

I notice you have 8 questions open, some of them 10 months old.  Don't leave all your threads open, this is against the forum policy.

spend your time reading other's solution, if it does help solve your problem, close it and assign the points to show respect and courtesy.

thanks for your cooperation!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question