PL/SQL procedure for table purging

[divroro12]

Hi Guys,

I have a table within my DB that's growing at a very fast rate & i need to do some maintenance to prevent constant filling of the underlying tablespace. Just one month worth of records is necessary.
This is what I have in mind.

1. Create an identical temporary table as original table
2. Insert last one month records into temp table
3. Truncate the original table
4. Insert temporary table records into original table
5. truncate temporsry table.

All of above should be in a PL/SQL procedure or Unix shell script.
Does anyone have anything similar already working?

Thanks in advance...

[davey23uk]

whats wrong with a simple delete statement, the space will get reused by new records and will save you the overhead of truncating tables only for them to fill again

[divroro12]

This has to be automated & delete will take forever....millions of records in the table as well as indexes & constraints

[LKBrwn_DBA]

This has to be automated & delete will take forever....millions of records in the table as well as indexes & constraints

The process you posted would take much more time than a delete.
If you have large table perhaps the best alternative is to use partitioning.

[PAVB]

Reverse purging - which is pretty much what your general procedure does - is a good alternative if you have to purge much of the data, much as in 90%+ of the data keeping just the remaining 10%- of it.

I wouldn't do #4... instead I would rename original table as *_old, rename temp table are the real deal then build indexes.