7 Replies Latest reply on Nov 5, 2013 8:13 PM by rp0428

    Get table partition name dynamically for given date range

    Bagiya
      Dear All,

      Could you please tell me how to get the partition name dynamicaly for given date range ?

      Thank you.
        • 2. Re: Get table partition name dynamically for given date range
          Peter Gjelstrup
          Hi Lakmal,

          Why? - If for dropping/truncating/ splitting partitions, I usually use a naming standard for partition_name.

          I ususally try to avoid using HIGH_VALUE of ALL_TAB_PARTITIONS, since this is of type LONG.

          Regards
          Peter
          • 3. Re: Get table partition name dynamically for given date range
            730428
            To manage this you have to write some pl/sql like this:
            CREATE TABLE TEST
            (
              MYDATE  DATE,
              ID                NUMBER
             )
            PARTITION BY RANGE (MYDATE)
            (  
              PARTITION P1 VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD')),
              PARTITION P2 VALUES LESS THAN (TO_DATE('2011-01-01', 'YYYY-MM-DD')),
              PARTITION XXX VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD')),
            );
            
            SQL> create or replace function get_part_name(p_date in date)
              2  return varchar2 is
              3  d date;
              4  retp varchar2(30);
              5  mind date:=to_date('4444-01-01','yyyy-mm-dd');
              6  str varchar2(32000);
              7  cursor c is
              8  select high_value, partition_name p
              9    from user_tab_partitions
             10   where table_name='TEST';
             11  begin
             12    for r in c loop
             13       str := r.high_value;
             14       execute immediate 'select '||str||' from dual' into d;     
             15       if p_date<d and d<mind then
             16          retp:=r.p;
             17          mind:=d;
             18       end if;
             19    end loop;
             20    return retp;
             21  end;
             22  /
            
            Function created.
            
            SQL> select get_part_name(sysdate) from dual;
            
            GET_PART_NAME(SYSDATE)
            ------------------------------------------------------------------------------
            P2
            
            SQL> select get_part_name(sysdate-300) from dual;
            
            GET_PART_NAME(SYSDATE-300)
            ------------------------------------------------------------------------------
            P1
            
            SQL> select get_part_name(sysdate+350) from dual;
            
            GET_PART_NAME(SYSDATE+350)
            ------------------------------------------------------------------------------
            XXX
            Max
            http://oracleitalia.wordpress.com
            • 4. Re: Get table partition name dynamically for given date range
              MichaelS
              SQL> select table_name,
                     partition_name,
                     to_date (
                        trim (
                           '''' from regexp_substr (
                                        extractvalue (
                                           dbms_xmlgen.
                                           getxmltype (
                                              'select high_value from all_tab_partitions where table_name='''
                                              || table_name
                                              || ''' and table_owner = '''
                                              || table_owner
                                              || ''' and partition_name = '''
                                              || partition_name
                                              || ''''),
                                           '//text()'),
                                        '''.*?''')),
                        'syyyy-mm-dd hh24:mi:ss')
                        high_value_in_date_format
                from all_tab_partitions
               where table_name = 'SALES' and table_owner = 'SH'
              
              TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE_IN_DATE_FORMAT
              ------------------------------ ------------------------------ -------------------------
              SALES                          SALES_1995                     01-JAN-96                
              SALES                          SALES_1996                     01-JAN-97                
              SALES                          SALES_H1_1997                  01-JUL-97                
              SALES                          SALES_H2_1997                  01-JAN-98                
              SALES                          SALES_Q1_1998                  01-APR-98                
              SALES                          SALES_Q2_1998                  01-JUL-98                
              SALES                          SALES_Q3_1998                  01-OKT-98                
              SALES                          SALES_Q4_1998                  01-JAN-99                
              SALES                          SALES_Q1_1999                  01-APR-99                
              SALES                          SALES_Q2_1999                  01-JUL-99                
              SALES                          SALES_Q3_1999                  01-OKT-99                
              SALES                          SALES_Q4_1999                  01-JAN-00                
              SALES                          SALES_Q1_2000                  01-APR-00                
              SALES                          SALES_Q2_2000                  01-JUL-00                
              SALES                          SALES_Q3_2000                  01-OKT-00                
              SALES                          SALES_Q4_2000                  01-JAN-01                
              SALES                          SALES_Q1_2001                  01-APR-01                
              SALES                          SALES_Q2_2001                  01-JUL-01                
              SALES                          SALES_Q3_2001                  01-OKT-01                
              SALES                          SALES_Q4_2001                  01-JAN-02                
              SALES                          SALES_Q1_2002                  01-APR-02                
              SALES                          SALES_Q2_2002                  01-JUL-02                
              SALES                          SALES_Q3_2002                  01-OKT-02                
              SALES                          SALES_Q4_2002                  01-JAN-03                
              SALES                          SALES_Q1_2003                  01-APR-03                
              SALES                          SALES_Q2_2003                  01-JUL-03                
              SALES                          SALES_Q3_2003                  01-OKT-03                
              SALES                          SALES_Q4_2003                  01-JAN-04                
              
              28 rows selected.
              1 person found this helpful
              • 6. Re: Get table partition name dynamically for given date range
                user1101464

                Its been a while but this helped me!  I added the min value for the partition using the lag function below.  Hope this helps someone as well.

                 

                select table_name,

                       partition_name,

                       to_date (

                          trim (

                             '''' from regexp_substr (

                                          extractvalue (

                                             dbms_xmlgen.

                                             getxmltype (

                                                'select high_value from all_tab_partitions where table_name='''

                                                || table_name

                                                || ''' and table_owner = '''

                                                || table_owner

                                                || ''' and partition_name = '''

                                                || partition_name

                                                || ''''),

                                             '//text()'),

                                          '''.*?''')),

                          'syyyy-mm-dd hh24:mi:ss')

                          high_value_in_date_format,

                          lag (

                          to_date (

                          trim (

                             '''' from regexp_substr (

                                          extractvalue (

                                             dbms_xmlgen.

                                             getxmltype (

                                                'select high_value from all_tab_partitions where table_name='''

                                                || table_name

                                                || ''' and table_owner = '''

                                                || table_owner

                                                || ''' and partition_name = '''

                                                || partition_name

                                                || ''''),

                                             '//text()'),

                                          '''.*?''')),

                          'syyyy-mm-dd hh24:mi:ss')+1) over (order by partition_position) low_value_in_date_format

                  from all_tab_partitions
                where table_name = 'SALES' and table_owner = 'SH'

                 

                TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE_IN_DATE_FORMAT          LOW_VALUE_IN_DATE_FORMAT
                ------------------------------ ------------------------------ -------------------------          -------------------------
                SALES                          SALES_1995                     01-JAN-96               
                SALES                          SALES_1996                     01-JAN-97                          02-JAN-96
                SALES                          SALES_H1_1997                  01-JUL-97                          02-JAN-97
                SALES                          SALES_H2_1997                  01-JAN-98                          02-JUL-97

                ...

                ...

                ...

                • 7. Re: Get table partition name dynamically for given date range
                  rp0428

                  DEAD THREAD!

                   

                  Please don't resurrect 3 1/2 year old threads.

                   

                  It's great that you want to contribute to the forums but your contributions will be more valuable if you focus on recent, unanswered threads.