karencita78
asked on
ora-01562
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
Please I need your help soon.
Karen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
You might also have your DBA check the rollback tablespace, and make sure it is large enough.
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
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
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.
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!
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!
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.