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
Printable View
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