DBAs,

I am working on a purge project and need to code pl/sql to purge the large tables
Need some help with massive delete with commit size.

The coding should satisfy ;
1)the commit size e.g. 5000
2)might use the bulk collect to improve the performance.

My situation is ;

all the real delete command will like this: ( it is dynamic query. The table name and the partition is dynamic, not static)

execute immediate '

delete from '||purge_table_fq||'

where pmt_id in (

select pmt_id from '||root_table_fq||' partition('||oldest_partition||')

where trunc(months_between(sysdate,pmt_archive_date))

> '||retention_months||'

and pmt_id not in (select pmt_id from bm.cbpayment_inquiry)

)

';

How can I code with commit size( if possible, also use the bulk collect)

If anyone has the similar coding experience, would you please provide a sample code?

Thanks a lot.