11g automagic partition creations

11g introduces this new interval partitioning ..bye-bye to all age-old partition maintenance script to create new partitions and capturing unexpected data into "maxvalue" bucket.

all you have to tell Oracle is your logic of partitioning..ie INTERVAL key.

See it in action here..

SQL>connect venkat/venkat

-- range partition by number
-- -------------------------

Create table TEST_AUTO_PARTITIONING_1
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c1)
interval(30) -- INTERVAL specified as 30
(
partition part1 values less than (100),
partition part2 values less than (200),
partition part3 values less than (300)
)
/


SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

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

-- insert 3 rows (50,150 and 250)
SQL> select * from TEST_AUTO_PARTITIONING_1
2 /

C1 C2 C3
---------- ---------- ---------
50
150
250

SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

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

SQL> insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n);
Enter value for n: 350
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (350)

1 row created.

SQL> /
Enter value for n: 450
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (450)

1 row created.

SQL> /
Enter value for n: 550
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (550)

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TEST_AUTO_PARTITIONING_1');
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_1';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 1
PART2 1
PART3 1
SYS_P41 1 -- system generated
SYS_P42 1 -- system generated
SYS_P43 1 -- system generated

6 rows selected.

to continue in next post: Interval partitioning for date column