3 Replies Latest reply: Jul 12, 2013 2:17 PM by Ishan RSS

    how to search data dictionary for interval partitions?

    Guess2

      Oracle 11.2.0.3

       

      When I query DBA_PART_TABLES.PARTITIONING_TYPE it says 'range'. I want to be able to differential an interval from other range based partitions in the data dictionary?


      used this example. shows 'range'

       

      http://www.rittmanmead.com/2008/09/investigating-oracle-11g-interval-partitioning/

       

      Oracle Docs list 'SYSTEM' as a partitioning_type which I thought would be intervals.

      ALL_PART_TABLES

       

      [code]

       

        1   CREATE TABLE interval_sales

        2          ( prod_id        NUMBER(6)

        3          , cust_id        NUMBER

        4          , time_id        DATE

        5          , channel_id     CHAR(1)

        6          , promo_id       NUMBER(6)

        7          , quantity_sold  NUMBER(3)

        8          , amount_sold    NUMBER(10,2)

        9          )

      10       PARTITION BY RANGE (time_id)

      11       INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

      12         ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),

      13           PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),

      14           PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),

      15*          PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) )

      SQL>/

       

      Table created.

       

      Elapsed: 00:00:00.01

      SQL> select partitioning_type from user_part_tables where table_name = 'INTERVAL_SALES';

       

      PARTITION

      ---------

      RANGE

       

      [/code]

        • 1. Re: how to search data dictionary for interval partitions?
          SomeoneElse

          Part of the problem is that you're using 11.2 but the manual you linked to for ALL_PART_TABLES is for 10.1, before interval partitioning existed.

           

          SQL> CREATE TABLE interval_sales

            2         ( prod_id        NUMBER(6)

            3         , cust_id        NUMBER

            4         , time_id        DATE

            5         , channel_id     CHAR(1)

            6         , promo_id       NUMBER(6)

            7         , quantity_sold  NUMBER(3)

            8         , amount_sold    NUMBER(10,2)

            9         )

          10     PARTITION BY RANGE (time_id)

          11     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

          12       ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),

          13         PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),

          14         PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),

          15         PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) )

          16  ;

           

          Table created.

           

          SQL> select interval from user_part_tables;

           

          INTERVAL

          ---------------------------------------------------------------------------------

          NUMTOYMINTERVAL(1,'MONTH')

          • 2. Re: how to search data dictionary for interval partitions?
            Ishan

            I think there is a column named INTERVAL in USER_TAB_PARTITIONS.

             

            Check once, if it has any info

            • 3. Re: how to search data dictionary for interval partitions?
              Greg Spall
              CREATE TABLE junk1
                 ( id         NUMBER )
                 PARTITION BY RANGE (id)
                 INTERVAL(5)
                   ( PARTITION p0 VALUES LESS THAN (5),
                     PARTITION p1 VALUES LESS THAN (10),
                     PARTITION p2 VALUES LESS THAN (15),
                     PARTITION p3 VALUES LESS THAN (20) )
              /
              
              
              CREATE TABLE junk2
                 ( id         NUMBER )
                 PARTITION BY RANGE (id)
                   ( PARTITION p0 VALUES LESS THAN (5),
                     PARTITION p1 VALUES LESS THAN (10),
                     PARTITION p2 VALUES LESS THAN (15),
                     PARTITION p3 VALUES LESS THAN (20) )
              /
              
              
              select owner, table_name, partitioning_type, interval
                from dba_part_tables
               where table_name in ('JUNK1', 'JUNK2')
              /
              

              OWNER                          TABLE_NAME                     PARTITIONING_TYPE INTERVAL           

              ------------------------------ ------------------------------ ----------------- --------------------

              GREGS                          JUNK1                          RANGE             5                  

              GREGS                          JUNK2                          RANGE                 

                           

               

               

              2 rows selected.