Printable View
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:
Originally Posted by Sathy
ummm, no!
huh? Any why would that cause YOU to have an ORA-1555?Quote:
Originally Posted by vnktummala
double no.Quote:
Originally Posted by Sathy
If you are deleting more than 60 % of the rows and the table is very big, then consider the following option.Quote:
Originally Posted by minal_yawale
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.
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".Quote:
Originally Posted by dbasan
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...
CTASQuote:
Originally Posted by tamilselvan
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...
Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.Quote:
Originally Posted by ixion
Do you have a test environment?
There are tools and/or scripts to reverse engineer all the dependencies. It's not that hard.
How about using a PL/SQL collection and then doing the delete with a forall????
Still you may get 1555 error if you use LIMIT in the collection.Quote:
Originally Posted by gandolf989
Tamil
Then how do you suggest the OP reclaim the space? Which is the reason for the deletes, and 1555 in the first place.Quote:
Originally Posted by dbasan
Best option is to add another data file or better yet resize the existing one.
Well, there is the new 10g commands that can lower the HWM. But he did not indicate the release.
SEARCH FOR DELETE _COMMIT PROCEDURE on metalink . It will do the trick.
You need to externalize the loop from the database. For example, if you need to delete 5 years of data, you can delete day by day with something like:
Code:declare
start_dt DATE;
begin
start_dt = to_date('01/01/2000','mm/dd/yyyy');
while start_dt < trunc(sysdate-365) loop
delete from yourTab where myDate = start_dt;
commit;
end loop;
end;
How about partitioning the table and removing the partitions with old data.
Just a thought, let me know whether it will work fine..
ITS URGENT!!!
Earlier i was trying to delete the records with the following.
DECLARE
row_num NUMBER:=0;
total_num NUMBER:=0;
CURSOR del_record_cur IS
SELECT ROWID
FROM table_name
WHERE DATE='01-JAN-2002';
BEGIN
FOR rec IN del_record_cur LOOP
DELETE FROM table_name
WHERE ROWID=rec.ROWID;
total_num:=total_num+1;
row_num:=row_num+1;
IF(row_num>=300) THEN
COMMIT;
row_num:=0;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total_num ||'records from table_name ');
END;
/
Then with this also i was getting the same error. so i thought because of frequent commit there might be this problem. so i have tried another one
DELETE FROM TABLE_NAME
WHERE DATE='01-JAN-2002'
AND ROWNUM<5;
but same error was thr.
Also i 've made undo_retention=1600.
The free size in undotbs is about 3 GB.
What should be the size of rollback segments ,if i have to delete about 4000 rows for single date.
Please provide the probable solution considering above clarifications.
I would not suggest for deleting few rows and commit frequently.
How ever, if you like it, Change the cursor:
CURSOR del_record_cur IS
SELECT ROWID
FROM table_name
WHERE DATE='01-JAN-2002'
order by rowid;
This will reduce chances of getting 1555 error, but will not totally eliminate.
Tamil
Better yet -- Get rid of the select!
Try just a delete:
DELETE FROM table_name WHERE DATE='01-JAN-2002';
commit;
However, performing a Delete DOES NOT RECLAIM space in the tablespace. You will be able to add to the table where the delete occurred. The space is not released for useage for other tables in the tablespace.
---------------------------------------------------------------------------------
How many rows are in the table?
OR just do the CTAS approach:
create table table_copy as select *
from your_table
where date > to_date('01-jan-2002');
( this will reclaim the space! )
truncate table your_Table;
insert into your_table (select * from table_copy);
Drop table table_copy;
******** WARNING ****
DO NOT SCRIPT THIS. Run it manually.
Take an export of the table prior to running the commands.
*********************
Can anybody reply me?
How about partitioning the table if the data is huge and then removing the partiton which contains old data?
1. That is a design change.Quote:
Originally Posted by tabreaz
2. There may not be enough space to accomplish the partitioning without first clearing the table.
3. good idea, requiring thorough application testing: (maybe vendor table etc..)..
You can also try this:
It's not likely to be faster than a straight delete,Code:SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
LOOP
DELETE table_name
WHERE DATE='01-JAN-2002'
AND rownum < 10001;
DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows deleted!');
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
COMMIT;
END;
/
but it should work without getting the snapshot too old error message.
why? A table is a table is a table. You'd have to check the query plans, but assuming you picked a decent partitioning key, you're all set.Quote:
Originally Posted by ixion
not true, just exchange partition with the big table. segments stay the same, just the data dictionary is updated.Quote:
2. There may not be enough space to accomplish the partitioning without first clearing the table.
definitely requires testing, although seems like a lot of work just to delete some data.Quote:
3. good idea, requiring thorough application testing: (maybe vendor table etc..)..
This is the second best solution I've seen.Quote:
Originally Posted by gandolf989
Beats getting the second best poker hand at a table. ;)Quote:
Originally Posted by marist89
As mentioned in the earlier replies; do have couple of big size rollback segments. Then execute the delete query by filtering the records by date or any numeric values etc... As it claims the table has got a good number of records.. the deletion cause rollback segment population. To maintain the rollback segment without causing the error the data inserting into rollback segment needs to be controlled; this can be possible by filtering the records in the delete statement.
no. You're not getting the ORA-1555 because of the number of deletes, you're getting it because you can't get a consistent view of the data. You have to externalize the loop by using a "calculatable" entity or an endless loop doing deletes in small chunks until there are no records left. These two methods have already been shown.Quote:
Originally Posted by helloravi
This block will be helpful, if you have sufficient memory.
Code:declare
type tab_type is table of tablename%rowtype;
lobj tab_type;
begin
select *
bulk collect into lobj
from tablename
where DATE <> '01-JAN-2002';
execute immediate 'create table newtable as select * from tablename
where 1 = 2';
execute immediate 'drop table tablename';
forall i in 1..lobj.count
insert into newtable
values lrtn(i);
commit;
end;
A Partitioned table is not a table....Quote:
Originally Posted by marist89
A table that is partitioned is a different storage mechanism. Requiring appropriate infrastructure to maintain it. That is unless you have a fixed mechanism and fixed partitioning. Implementing rolling date partitioning takes a bit of code to make work correctly. Something that is probably out of the original Posters technical skill to implement considering the question asked.
I think you've missed the point of the question I was answering. Please re-read, I'm sure He'd run into space issues due to re-organizing a single large unpartitioned table into a table that is partitioned into smaller more manageable units.Quote:
Originally Posted by marist89
Well at least we both agree testing is a good idea.Quote:
Originally Posted by marist89
The OP has a disk space issue. The ora-1555 is a symptom of the problem. Not the problem.Quote:
Originally Posted by minal_yawale
What is the goal here?
1. Reclaim space within the table?
Use the delete loops posted above from gandolf...
2. Reclaim space for the tablespace ?
Use the Ctas approach from above. (see posts #7 & #21 )
3. Fix the problem ?
Add more disk and increase the tablespace.
Implement partitioning and data archiving strategy.
I 100% agree, except before you said there was a design change involved.Quote:
Originally Posted by ixion
I don't work in the world of assumptions. The point I was making was to convert a non-partitioned table to a partitioned table you don't need any more space than you currently have since the segments stay the same.Quote:
Originally Posted by ixion
Your assuming the segments are staying the same. If you are partitioning lets say for the sake of argument, on a date range. And each partition is bounded by say a month. Then the original table could not be swapped into such a partitioning scheme since the table would contain data that is out of bounds for the particular range...Quote:
Originally Posted by marist89
Now if you have say 20 tables identical in structure except for the data contents and name. Say each is named tbl_YYYYMMDD Where each table contains data for the representative naming standard. Then swapping these into a partitioned table is trivial and is indeed identical segment wise.
Maybe we are saying the same thing, but differently. ;)
Ken
no I'm not,that's the way it works.Quote:
Originally Posted by ixion
exactly what I said.Quote:
Then swapping these into a partitioned table is trivial and is indeed identical segment wise.
To convert a non-partitioned table to a partitioned table with only one partition bounded by maxvalue, it is a simple command that only updates the data dictionary.
Yes in this 1 special case the segments stay the same. But its kind of pointless to partition in this manner. You're better off with the non partitioned table. Please explain how this is going to allow this person to drop the older data partition if there is only one partition with all of the data? (see post #23)Quote:
Originally Posted by marist89
Just because you can do this, doesn't mean you should. Well except maybe you might split the partition off to the older stuff and the newer stuff. And then drop the older stuff... But that takes space due to allocation of new segments and then row movement.