PL/SQL procedure for table purging
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PL/SQL procedure for table purging

  1. #1
    Join Date
    Nov 2006
    Posts
    158

    PL/SQL procedure for table purging

    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...
    Regards,

    divroro12

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Nov 2006
    Posts
    158
    This has to be automated & delete will take forever....millions of records in the table as well as indexes & constraints
    Regards,

    divroro12

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458

    Cool

    Quote Originally Posted by divroro12 View Post
    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width