-
Originally Posted by ixion
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...
Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.
"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????
-
Originally Posted by gandolf989
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.
Tamil
-
Originally Posted by dbasan
Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.
Then how do you suggest the OP reclaim the space? Which is the reason for the deletes, and 1555 in the first place.
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:
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;
Jeff Hunter
-
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.
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
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
Last edited by tamilselvan; 07-11-2006 at 09:36 AM.
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
|