CODEWITHSUNDEEP
oracle10g
user630152
user630152

Reputation: 11

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 "" too small Error.

please give me a solution.

Upvotes: 1

Views: 25432

Answers (4)

Rizwan Basheer
Rizwan Basheer

Reputation: 137

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

Solution:

Step 1) check size of undo_retention parameter (Step 2). 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.

You can also set the Undo Tablespace to Autoextend on.

then try again.

Upvotes: 2

Swapna Mohan
Swapna Mohan

Reputation: 175

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.

Upvotes: 1

Olaf
Olaf

Reputation: 6289

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.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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;

Upvotes: 1

Related Questions