Partition High Value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Partition High Value

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Partition High Value

    I have a table that is partitioned by month. I only want 3 months worth of data in it. I am trying to write a program that identifies the paritions that hold values older then 3 months and drop them. This script would run once a month.

    When I try to use the high value column in dba_tab_partitions it is long and I can't sort on it to get the last three. Any ideas how I could get the partition name of the partitions that I want dropped?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you have used sensible partition names, then it would be easy.

    for example if your partition for July 2003 is called "P2003_07" then you can perform ...

    Code:
    begin
       execute immediate
          'alter table
             my_table
          drop partition '||
          to_char(add_months(sysdate,-4),'"P"YYYY"_"MM');
    end;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Instead of the high_value, use the partition_position from dba_Tab_partitions.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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