Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.
Originally Posted by ixion
"What is past is PROLOGUE"
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????
this space intentionally left blank
Still you may get 1555 error if you use LIMIT in the collection.
Originally Posted by gandolf989
Then how do you suggest the OP reclaim the space? Which is the reason for the deletes, and 1555 in the first place.
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.
One, who thinks that the other one who thinks that know and does not know, does not know either!
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:
start_dt = to_date('01/01/2000','mm/dd/yyyy');
while start_dt < trunc(sysdate-365) loop
delete from yourTab where myDate = start_dt;
How about partitioning the table and removing the partitions with old data.
Just a thought, let me know whether it will work fine..
Earlier i was trying to delete the records with the following.
CURSOR del_record_cur IS
FOR rec IN del_record_cur LOOP
DELETE FROM table_name
DBMS_OUTPUT.PUT_LINE('Deleted ' || total_num ||'records from table_name ');
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
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.
Last edited by minal_yawale; 07-11-2006 at 02:18 AM.
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
order by rowid;
This will reduce chances of getting 1555 error, but will not totally eliminate.
Last edited by tamilselvan; 07-11-2006 at 09:36 AM.
Click Here to Expand Forum to Full Width