Commit after deletion of every 5000 rows [message #667620] |
Mon, 08 January 2018 23:26 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi All,
I have to write a PL/SQL block that deletes records based on condition. But I want it should commit after every 5000 rows and also I have to do deletion near about 120 tables .So how do I write a generic scripts for deletion of records.
Please suggest.
Thanks In Advance.
|
|
|
|
|
|
|
|
|
|
|
|
Re: Commit after deletion of every 5000 rows [message #667679 is a reply to message #667672] |
Wed, 10 January 2018 07:49 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
That's the beauty of CTAS. Your indexes and grants are not effected. You would do something like this (example code follows, adjust for your actual conditions)
create table backup_my_table as select * from my_table where date_column > add_month(trunc(sysdate,'MONTH'),-12);
truncate table my_table;
insert into my_table select * from backup_my_table;
commit;
drop table backup_my_table;
The truncate of the table would take just a couple of seconds. the insert would rebuild all the indexes as the insert happens. The one problem with this is when you have multiple table with foreign key constraints, however before you start the work you can easily disable the existing constraints by using
alter table my_table DISABLE constraint constraint_name;
and then turn it back on afterwords by using
alter table my_table ENABLE constraint constraint_name;
With the constraints disabled oracle will not do any checking until they are turned back on.
This is only one possible method. The real solution is to use partitioned tables with the partition build on the month.
|
|
|
Re: Commit after deletion of every 5000 rows [message #667680 is a reply to message #667679] |
Wed, 10 January 2018 07:54 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Bill B wrote on Wed, 10 January 2018 08:49
alter table my_table DISABLE constraint constraint_name;
and then turn it back on afterwords by using
alter table my_table ENABLE constraint constraint_name;
And don't forget about doing the same with triggers.
|
|
|
|
|
|
|
Re: Commit after deletion of every 5000 rows [message #668466 is a reply to message #667697] |
Mon, 26 February 2018 12:10 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
BlackSwan wrote on Wed, 10 January 2018 22:34>How Can I pass this multiple table name with above block.
One possible way is to have the table_name in column of a (GTT?) table
>I have to delete all 10 years older data from all tables at schema level.
How do you identify rows older than 10 years old from each table?
Based on a column that stores date(s) relevant to business transaction or the typical WHO columns. Use of of these columns and then subtract it from SYSDATE. That should give you an insight if the data is older or less than or equal to 10 years.
[Updated on: Mon, 26 February 2018 12:12] Report message to a moderator
|
|
|
|