Home » SQL & PL/SQL » SQL & PL/SQL » Commit after deletion of every 5000 rows (Oracle11g)
Commit after deletion of every 5000 rows [message #667620] Mon, 08 January 2018 23:26 Go to next message
ssyr
Messages: 50
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 #667622 is a reply to message #667620] Tue, 09 January 2018 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suggest you don't do that.
Anyway, here's an example:
SQL> create table t as select * from dual connect by level <= 12000;

Table created.

SQL> set serveroutput on
SQL> begin
  2    loop
  3      delete &table where rownum <= 5000;
  4      exit when sql%rowcount = 0;
  5      dbms_output.put_line(sql%rowcount||' rows deleted');
  6      commit;
  7    end loop;
  8  end;
  9  /
Enter value for table: t
5000 rows deleted
5000 rows deleted
2000 rows deleted

PL/SQL procedure successfully completed.
Re: Commit after deletion of every 5000 rows [message #667627 is a reply to message #667622] Tue, 09 January 2018 01:47 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Thanks for your reply.

I am not able to check rownnum <5000 condition's I have check data with different condition.

Thanks.
Re: Commit after deletion of every 5000 rows [message #667628 is a reply to message #667627] Tue, 09 January 2018 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Then add your conditions.

Re: Commit after deletion of every 5000 rows [message #667658 is a reply to message #667628] Wed, 10 January 2018 00:09 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Hi Michel,

Could you please suggest me how can I delete records which does not create logging so much.
I have to delete all 10 years older data from all tables at schema level.
For that thing I need to fire frequently commit statement.

How can I do this?

Thanks in advance.

Re: Commit after deletion of every 5000 rows [message #667661 is a reply to message #667658] Wed, 10 January 2018 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about another way: could you CTAS (CREATE TABLE AS SELECT) and create tables which contain data you want to keep. Then TRUNCATE old tables (which is efficient) and move new data back?
Re: Commit after deletion of every 5000 rows [message #667662 is a reply to message #667661] Wed, 10 January 2018 01:29 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Thanks for quick follow up.

But it is not one time event. I have to do it on monthly basis. Also not for single table it is schema level.

So Is this good option to do CTAS.

Re: Commit after deletion of every 5000 rows [message #667664 is a reply to message #667662] Wed, 10 January 2018 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So you'll delete "old" rows every month? In that case, check whether partition option is available in your database. It might be a good option; partition those tables on DATE column values (i.e. months) and simply truncate that partition once you don't need it. That won't help in your current problem (although, CTAS might make it faster than slow delete you currently use), but might in the future.
Re: Commit after deletion of every 5000 rows [message #667671 is a reply to message #667662] Wed, 10 January 2018 06:50 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
ssyr wrote on Wed, 10 January 2018 01:29
Thanks for quick follow up.

But it is not one time event. I have to do it on monthly basis. Also not for single table it is schema level.

So Is this good option to do CTAS.

Why would the fact it is not a one-time event come into play? That's what scripts and job schedulers were created for.
Re: Commit after deletion of every 5000 rows [message #667672 is a reply to message #667671] Wed, 10 January 2018 06:55 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Thanks For reply.

How Can I perform this using Job Scheduler or Scripts? If I go for CTAS then I have to also check all grants, Indexes created on tables.

Any idea on this. Please suggest.
Re: Commit after deletion of every 5000 rows [message #667679 is a reply to message #667672] Wed, 10 January 2018 07:49 Go to previous messageGo to next message
Bill B
Messages: 1814
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 Go to previous messageGo to next message
joy_division
Messages: 4909
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 #667683 is a reply to message #667680] Wed, 10 January 2018 08:22 Go to previous messageGo to next message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
Thanks joy, I forgot to mention the triggers.
Re: Commit after deletion of every 5000 rows [message #667695 is a reply to message #667683] Wed, 10 January 2018 22:24 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
Thanks for your suggestion.

I will try with above suggestion.
Re: Commit after deletion of every 5000 rows [message #667696 is a reply to message #667695] Wed, 10 January 2018 22:28 Go to previous messageGo to next message
ssyr
Messages: 50
Registered: January 2017
Member
HI,

Can I used EXECUTE IMMEDIATE statement. As I have to pass table name to the block.

How Can I pass this multiple table name with above block.

Thanks in advance.
Re: Commit after deletion of every 5000 rows [message #667697 is a reply to message #667696] Wed, 10 January 2018 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
>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?
Re: Commit after deletion of every 5000 rows [message #668466 is a reply to message #667697] Mon, 26 February 2018 12:10 Go to previous messageGo to next message
buggleboy007
Messages: 198
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

Re: Commit after deletion of every 5000 rows [message #668467 is a reply to message #668466] Mon, 26 February 2018 15:23 Go to previous message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
yes you can use execute immediate for your task
Previous Topic: string format
Next Topic: Table Archive and Purge
Goto Forum:
  


Current Time: Sun Nov 18 23:28:39 CST 2018