Example on interval partition (11gR1 onwards)

SQL> desc sh.sales;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)


SQL> create table sales
2 (prod_id number not null,
3 cust_id number not null,
4 time_id date not null,
5 channel_id number not null,
6 promo_id number not null,
7 quantity_sold number(10,2) not null,
8 amount_sold number(10,2) not null)
9 partition by range(time_id)
10 interval (numtoyminterval(3,'month'))
11 store in (ts1,ts2,ts3,ts4)
12 (partition sales_q1_1998 values less than
13 (to_date('01-04-1998','dd-mm-yyyy'))
14 )
SQL> /

SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a15
SQL> set lin 80
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ---------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01

SQL> insert into sales select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.


SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')

16 rows selected.

SQL>

SQL> insert into sales values
2 (14,288,sysdate,3,999,1,1259.99);

1 row created.

SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P36 TS3 TO_DATE(' 2010-10-01 NUMTOYMINTERVAL(3,'MONTH')

17 rows selected.

SQL> rollback;

Rollback complete.

SQL> @retrieve_partition.sql
SQL> col partition_name for a20
SQL> col tablespace_name for a20
SQL> col high_value for a20
SQL> col interval for a30
SQL> set lin 100
SQL> set long 20
SQL> select a.partition_name,a.tablespace_name,a.high_value,
2 decode(a.interval,'YES',b.interval) interval
3 from user_tab_partitions a, user_part_tables b
4 where a. table_name='SALES'
5 and a.table_name=b.table_name
6 order by a.partition_position
7 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SALES_Q1_1998 USERS TO_DATE(' 1998-04-01
SYS_P21 TS2 TO_DATE(' 1998-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P22 TS3 TO_DATE(' 1998-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P23 TS4 TO_DATE(' 1999-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P24 TS1 TO_DATE(' 1999-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P25 TS2 TO_DATE(' 1999-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P26 TS3 TO_DATE(' 1999-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P27 TS4 TO_DATE(' 2000-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P28 TS1 TO_DATE(' 2000-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P29 TS2 TO_DATE(' 2000-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P30 TS3 TO_DATE(' 2000-10-01 NUMTOYMINTERVAL(3,'MONTH')

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INTERVAL
-------------------- -------------------- -------------------- ------------------------------
SYS_P31 TS4 TO_DATE(' 2001-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P32 TS1 TO_DATE(' 2001-04-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P33 TS2 TO_DATE(' 2001-07-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P34 TS3 TO_DATE(' 2001-10-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P35 TS4 TO_DATE(' 2002-01-01 NUMTOYMINTERVAL(3,'MONTH')
SYS_P36 TS3 TO_DATE(' 2010-10-01 NUMTOYMINTERVAL(3,'MONTH')

17 rows selected.

SQL>