--evaluate number of rows to delete select count(*) from WLI_NESOA2_REPORTING_ARCHIVE; select count(*) from WLI_NESOA2_REPORTING_ARCHIVE where EventOccuredOn < '2013-05-10'; --prepare empty table containing those rowids drop table tobedeleted; --populate table create table tobedeleted as SELECT rowid as MYROWID from WLI_NESOA2_REPORTING_ARCHIVE where EventOccuredOn < '2013-05-10'; --evaluate if numbers match select count(*) from tobedeleted; DECLARE nCount NUMBER; BEGIN nCount := 0; LOOP --create subset of 10000 to delete execute immediate 'create table TOBEDELETEDNOW as select MYROWID from tobedeleted where rownum <= 10000'; --delete first 10000 execute immediate 'delete from WLI_NESOA2_REPORTING_ARCHIVE where rowid in (select MYROWID from TOBEDELETEDNOW )'; commit; --remove the already purged records from list execute immediate 'delete from tobedeleted where MYROWID in (select MYROWID from TOBEDELETEDNOW)'; nCount := sql%rowcount; execute immediate 'drop table TOBEDELETEDNOW'; commit; DBMS_OUTPUT.PUT_LINE('deleted records: ' || to_char(ncount) ); commit; EXIT WHEN nCount = 0; END LOOP; end; /
Thursday, July 18, 2013
Oracle DB, purge large tables
I have a huge table WLI_NESOA2_REPORTING_ARCHIVE and I need to purge 10000 rows at a time for all records older than 2013-05-10. It's easy if you use a ROWID table and empty it progressively.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment