ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-01555: snapshot too old: rollback segment number with name "" too small

When I calling procedure after that I am writing commit.

Actually, I want to copy the data from one table to a temporary table. but data is not inserting and giving ORA-01555: snapshot too old: rollback segment number with name "" 开发者_Go百科too small Error.

please give me a solution.

The easiest way to copy data from one table to another is to create the new table like this:

create table table2 as select * from table1;

You get "ORA-01555: snapshot too old: rollback segment number with name" usually when the SQL runs too long. This is because rollback records needed by a reader for consistent read are overwritten by other writers. It would be helpful if you share your code snippet.

ORA-01555: snapshot too old: rollback segment number 9 with name "SYSSMU 9*

Solution :

Step :1) check size of undo_retention parameter . if it is 900. increase to 3600 by doing step 3.

Step 2 : SQL> show parameter undo_retention;

NAME TYPE VALUE

undo_retention integer 900

Step 3 :SQL> ALTER SYSTEM SET UNDO_RETENTION = 3600;

System altered.

Increase Undo Table space to Auto Extended on.

then try again.

How long does it take from the beginning of the operation to the point when the Oracle error occurs? In situations that I've seen it the time could be measured in hours and it was a good indication that the work should have been cut up in chunks with commits after every chunk. If that time is short, you might be just running out of the disk space or have your Oracle instance misconfigured.