One of the nice features in Oracle 12c is the easier mainteannace of partitioning operations.
In pre-12c oracle databases , to add , split , merge the partitions , the commands has to be done per partition . Starting with Oracle 12c , the operations can be done in one single statement.
Let us see an example.
We will create a table range based partitioned table called t_part with two partitions.
SQL> Create table t_part
2 ( productcode int , productname varchar2(150) , company varchar2(150))
3 partition by range (productcode)
4 (
5 partition product_1000 values less than (1000) ,
6 partition product_2000 values less than (2000)
7 )
8 ;
Table created.
To add partitions in prior to 12c , we would need to issue three statements as below.
SQL> alter table t_part
2 add partition product_3000 values less than ( 3000) ;
Table altered.
SQL> alter table t_part
2 add partition product_4000 values less than ( 4000) ;
Table altered.
SQL> alter table t_part
2 add partition product_5000 values less than ( 5000) ;
Table altered.
For the similar operations in oracle 12c , we can do it in one statement as below.
In the following example , I am adding five partitions in one statement.
2 add
3 partition product_6000 values less than ( 6000) ,
4 partition product_7000 values less than ( 7000) ,
5 partition product_8000 values less than ( 8000) ,
6 partition product_9000 values less than ( 9000),
7 partition product_10000 values less than ( 10000),
8 partition product_15000 values less than ( 15000)
9 ;
Table altered.
SQL> insert into t_part
2 select object_id as productcode
3 , object_name as productname
4 , owner as company from all_objects
5 where object_id <15000 span="">
6 ;
14602 rows created.
SQL> exec dbms_stats.gather_table_stats ( user , 'T_PART') ;
PL/SQL procedure successfully completed.
SQL> select table_name , partition_name , num_rows ,
2 last_analyzed
3 from user_tab_partitions where table_name ='T_PART'
4 order by 2 ;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------------------- ---------- ---------
T_PART PRODUCT_1000 889 20-DEC-14
T_PART PRODUCT_10000 986 20-DEC-14
T_PART PRODUCT_15000 4978 20-DEC-14
T_PART PRODUCT_2000 924 20-DEC-14
T_PART PRODUCT_3000 1000 20-DEC-14
T_PART PRODUCT_4000 1000 20-DEC-14
T_PART PRODUCT_5000 1000 20-DEC-14
T_PART PRODUCT_6000 985 20-DEC-14
T_PART PRODUCT_7000 944 20-DEC-14
T_PART PRODUCT_8000 928 20-DEC-14
T_PART PRODUCT_9000 968 20-DEC-14
11 rows selected.
Similarly , I can split the partition in one statement as well as shown below.
SQL> alter table t_part
2 split
3 partition product_15000
4 into
5 (
6 partition product_11000 values less than ( 11000) ,
7 partition product_12000 values less than ( 12000) ,
8 partition product_13000 values less than ( 13000) ,
9 partition product_14000 values less than ( 14000) ,
10 partition product_15000
11 ) ;
Table altered.
SQL> select table_name , partition_name , num_rows ,
2 last_analyzed
3 from user_tab_partitions where table_name ='T_PART'
4 order by 2 ;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------------------- ---------- ---------
T_PART PRODUCT_1000 889 20-DEC-14
T_PART PRODUCT_10000 986 20-DEC-14
T_PART PRODUCT_11000
T_PART PRODUCT_12000
T_PART PRODUCT_13000
T_PART PRODUCT_14000
T_PART PRODUCT_15000
T_PART PRODUCT_2000 924 20-DEC-14
T_PART PRODUCT_3000 1000 20-DEC-14
T_PART PRODUCT_4000 1000 20-DEC-14
T_PART PRODUCT_5000 1000 20-DEC-14
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------------------- ---------- ---------
T_PART PRODUCT_6000 985 20-DEC-14
T_PART PRODUCT_7000 944 20-DEC-14
T_PART PRODUCT_8000 928 20-DEC-14
T_PART PRODUCT_9000 968 20-DEC-14
15 rows selected.
This enhancement is one of the best in Oracle 12c.
Welcome your comments.
