11g automagic partition creations - part2

its a bit different when you range partition by date column..you cannot specify a constant as your interval

SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(30)
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /
Create table test_auto_partitioning_2
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Instead specify as date-interval datatype so oracle is aware of what you are requesting.. Rest is pretty much the same..



SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(numtoyminterval(1,'MONTH'))
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /

Table created.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1
PART2
PART3

SQL> Insert into TEST_AUTO_PARTITIONING_2(c3)
2 values (to_date('&mm/&dd/2007','mm/dd/yyyy'));
Enter value for mm: 09
Enter value for dd: 01
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/01/2007','mm/dd/yyyy'))

1 row created.


SQL> /
Enter value for mm: 09
Enter value for dd: 13
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/13/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 09
Enter value for dd: 17
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/17/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 10
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('10/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 11
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('11/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 12
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('12/16/2007','mm/dd/yyyy'))

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_AUTO_PARTITIONING_2');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 2
PART2 1
PART3 1
SYS_P44 1 -- auto generated
SYS_P45 1 -- auto generated

11g rocks!!