dropping partitions using date function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: dropping partitions using date function

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    dropping partitions using date function

    I am trying to create a script that drops partitions, which are older than
    "N' days.

    My partitions names are always in this format "P_2010_10_20" (P_YYYY_MM_DD)


    I want to do something like this, but the syntax is not correct:

    select partition_name from user_tab_partitions where table_name = 'XXX'
    and partition_name < (sysdate - Ndays)


    Can somebody help me with the correct syntax.

    Thanks to all who answer

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Cool

    Try this:
    Code:
    SELECT partition_name
      FROM user_tab_partitions
     WHERE table_name = 'XXX'
           AND partition_name < 'P_' || TO_CHAR ( (SYSDATE - 30), 'YYYY_MM_DD');
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    LK, works like great. Thanks

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