Ora-01555 Snapshot Too Old

[minal_yawale]

Hi All,

I need to delete large number of rows as my disk is having very less space. While deleting I'm getting following error -

ORA-01555 SNAPSHOT TOO OLD

for this I've increase the size of UNDO tablespace but still I'm getting the same error. I can not increase the size of UNDO tablespace more. Please let me know the probable solution for such situation.

Thanks in advance.

regards

Minal

[Sathy]

Use commit frequently....

--Sathy

[vnktummala]

Make sure that no other users are trying to fetch the rows that you are trying to delete. Increase UNDO_RETENTION parameter value ....

Thanks,

[davey23uk]

Use commit frequently....

--Sathy

ummm, no!

[marist89]

Make sure that no other users are trying to fetch the rows that you are trying to delete.

huh? Any why would that cause YOU to have an ORA-1555?

[marist89]

Use commit frequently....

double no.

[tamilselvan]

Hi All,

I need to delete large number of rows as my disk is having very less space. While deleting I'm getting following error -

ORA-01555 SNAPSHOT TOO OLD


Minal

If you are deleting more than 60 % of the rows and the table is very big, then consider the following option.
Create temp table from the original table in which you want to retain the rows.
Truncate original table.
Insert into original table from the temp table.

Tamil

[dbasan]

Tamil has given a work around.

To avoid ora1555:

1) Increase the size of rollback segment. (which you have already done)

2) Process a range of data rather the whole table. (need to do in your circumstances)

3) Add a big rollback segment and allot your transaction to this rbs.

4) There is also a possibility of rbs getting shrunk during the life of the query by setting optimal. (ruled out in your case)

5) Avoid frequent commits.

6) google out for other causes.

[waitecj]

3) Add a big rollback segment and allot your transaction to this rbs.

5) Avoid frequent commits.

When I saw UNDO tablespace, I was thinking AUM, no? Also, even if not, in step three you have said "Add a big rollback segment and allot your transaction to this rbs", but even if you did that how would you stop other trnasactions from using your rollback segment? By taking it offline, you say? Well, that would only work if you were doing the delete in one go (a single commit at the end), otherwise the rollback segment you assigned and then offlined would no longer be available. So then, step 5 (Avoid frequent commits), should actually read "Commit once at the end".

Small points, but important ones if the OP was going to use your tip top 5 step guide to avoiding 1555.... which probably wouldn't work...

[ixion]

If you are deleting more than 60 % of the rows and the table is very big, then consider the following option.
Create temp table from the original table in which you want to retain the rows.
Truncate original table.
Insert into original table from the temp table.

Tamil

CTAS
Then, Instead of insert, drop the original and rename the newly created table with less data! He'll also need to recreate triggers, indexes and FK's etc...