7 Replies Latest reply: Nov 5, 2013 2:13 PM by rp0428 RSS

    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.
        • 1. Re: Get table partition name dynamically for given date range
          Bagiya
          any one plz help me
          • 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.
                • 5. Re: Get table partition name dynamically for given date range
                  Bagiya
                  Thanks a lot michael
                  • 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.