-
Ora-01555 Snapshot Too Old
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
-
Use commit frequently....
--Sathy
-
Make sure that no other users are trying to fetch the rows that you are trying to delete. Increase UNDO_RETENTION parameter value ....
Thanks,
-
![Quote](images/misc/quote_icon.png) Originally Posted by Sathy
Use commit frequently....
--Sathy
ummm, no!
-
![Quote](images/misc/quote_icon.png) Originally Posted by vnktummala
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?
Jeff Hunter
-
![Quote](images/misc/quote_icon.png) Originally Posted by Sathy
Use commit frequently....
double no.
Jeff Hunter
-
![Quote](images/misc/quote_icon.png) Originally Posted by 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
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
-
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.
"What is past is PROLOGUE"
-
![Quote](images/misc/quote_icon.png) Originally Posted by dbasan
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...
Assistance is Futile...
-
![Quote](images/misc/quote_icon.png) Originally Posted by tamilselvan
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|