This discussion is archived
7 Replies Latest reply: Nov 5, 2013 12:13 PM by rp0428 RSS

Get table partition name dynamically for given date range

Bagiya Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    any one plz help me
  • 2. Re: Get table partition name dynamically for given date range
    Peter Gjelstrup Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot michael
  • 6. Re: Get table partition name dynamically for given date range
    user1101464 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points