PL/SQL procedure for table purging
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...
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
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.
Originally Posted by divroro12
If you have large table perhaps the best alternative is to use partitioning.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width