{"id":2973,"date":"2013-08-05T09:35:00","date_gmt":"2013-08-05T07:35:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/"},"modified":"2013-08-05T09:35:00","modified_gmt":"2013-08-05T07:35:00","slug":"oracle-12c-partitioning-enhancements-part-i-reference-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/","title":{"rendered":"Oracle Database 12c: Partitioning enhancements Part I &#8211; Reference partitioning"},"content":{"rendered":"<p><img decoding=\"async\" class=\"blog-image aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\" alt=\"\" \/><\/p>\n<p>Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.<\/p>\n<h3>TRUNCATE and EXCHANGE with the CASCADE option<\/h3>\n<p>With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when truncating or exchanging a partition.<\/p>\n<p>As an example, I created the two following objects on an Oracle 11g R2 and an Oracle 12c databases:<\/p>\n<ul>\n<li>A parent table TB_CUSTOMER containing a list of customers and the date of their inscription to an online shop<\/li>\n<li>A child-referenced table TB_INVOICE containing the id and date of invoices performed by customers on this shop.<\/li>\n<\/ul>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_CUSTOMER (\nCUST_ID number primary key,\nCUST_NAME varchar(25),\nCUST_DATE date)\nPARTITION BY RANGE (CUST_DATE)(\nPARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,\nPARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS,\nPARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')) NOCOMPRESS);<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_INVOICE (INV_ID number primary key,\nINV_DATE date,\nCUST_ID number not null,\nConstraint fk_custid foreign key (cust_id) references TB_CUSTOMER(cust_id) ON DELETE CASCADE)\nPartition by reference (fk_custid);<\/pre>\n<p>We can see that all three partitions defined in the TB_CUSTOMER creation time have automatically been created in the TB_INVOICE table too:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, partition_name from user_tab_partitions;\n\u00a0\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\n------------------------------ -----------------------------------\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12<\/pre>\n<p>This behavior is common to 11g R2 and 12c releases and is proper to the reference partitioning.<br \/>\nTo finalize my example, I have populated the table with customers and invoices:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2012','dd.mm.yyyy'));\nSQL&gt; insert into TB_CUSTOMER values (2,'SCOTT',to_date('26.04.2012','dd.mm.yyyy'));\nSQL&gt; insert into TB_CUSTOMER values (3,'TIGER',to_date('30.08.2012','dd.mm.yyyy'));\nSQL&gt; insert into TB_CUSTOMER values (4,'HR',to_date('27.06.2013','dd.mm.yyyy'));\nSQL&gt; insert into TB_CUSTOMER values (5,'BI',to_date('18.02.2011','dd.mm.yyyy'));\nSQL&gt; commit;<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; insert into TB_INVOICE values (1, to_date('21.04.2013','dd.mm.yyyy'),2);\nSQL&gt; insert into TB_INVOICE values (2, to_date('01.02.2013','dd.mm.yyyy'),2);\nSQL&gt; insert into TB_INVOICE values (3, to_date('16.06.2013','dd.mm.yyyy'),1);\nSQL&gt; insert into TB_INVOICE values (4, to_date('17.06.2013','dd.mm.yyyy'),2);\nSQL&gt; commit;<\/pre>\n<h4>Oracle 11g R2<\/h4>\n<p>I tried to truncate the partition containing customers registered in 2012 (P_2012_31_12). This partition features the customers OE, SCOTT and TIGER.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Alter table tb_customer truncate partition P_2012_31_12;\nAlter table tb_customer truncate partition P_2012_31_12\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\nERROR at line 1:ORA-02266: unique\/primary keys in table referenced by enabled foreign keys<\/pre>\n<p>The error ORA-02266 is returned because normally, the CASCADE option must be used when performing operations on data with references constraints. If I try to truncate the partition with the CASCADE option in 11g, I am not successful:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Alter table tb_customer truncate partition P_2012_31_12 cascade;\nAlter table tb_customer truncate partition P_2012_31_12 cascade\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\nERROR at line 1:ORA-14054: invalid ALTER TABLE TRUNCATE PARTITION option<\/pre>\n<h4>Oracle 12c<\/h4>\n<p>I performed the same test on the Oracle 12c database:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Alter table tb_customer truncate partition P_2012_31_12 cascade;\nTable truncated.<\/pre>\n<p>The statement was accepted. To check if the cascade operation is effective, we can count the number of rows for both TB_CUSTOMER and TB_INVOICE tables:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select count (*) from tb_customer;\n\u00a0\nCOUNT(*)\n--------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2<\/pre>\n<p>The customers OE, SCOTT, and TIGER, registered in 2012 (partition P_2012_31_12) were\u00a0 removed from the table TB_CUSTOMER.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select count (*) from tb_invoice;\u00a0\nCOUNT(*)\n--------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/pre>\n<p>The corresponding invoices were removed from the table TB_INVOICE with the cascade option.<\/p>\n<h3>Support of interval partitioning for parent tables<\/h3>\n<p>Prior to Oracle 12c, it was not possible to use an interval-partitioned table as a parent table for reference partitioning.<\/p>\n<h4>Oracle 11g R2<\/h4>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_CUSTOMER (\nCUST_ID number primary key,\nCUST_NAME varchar(25),\nCUST_DATE date\n)PARTITION BY RANGE (CUST_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))(\nPARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),\nPARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),\nPARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')));\nTable created<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_INVOICE (\nINV_ID number primary key,\nINV_DATE date,CUST_ID number not null,\nConstraint fk_custid foreign key (cust_id) references TB_CUSTOMER (cust_id) ON DELETE CASCADE\n)\nPartition by reference (fk_custid);<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Create table TB_INVOICE (\n*\nERROR at line 1:ORA-14659: Partitioning method of the parent table is not supported<\/pre>\n<p>&nbsp;<\/p>\n<p>The error ORA-14659 is explicit: interval partitioning is not supported for the parent table.<\/p>\n<h4>Oracle 12c<\/h4>\n<p>This limitation has gone with Oracle 12c.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; drop table tb_customer;\nTable dropped.<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_CUSTOMER (\nCUST_ID number primary key,\nCUST_NAME varchar(25),\nCUST_DATE date\n)PARTITION BY RANGE (CUST_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))(\nPARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),\nPARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),\nPARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')));\nTable created.<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; Create table TB_INVOICE (\nINV_ID number primary key,\nINV_DATE date,\nCUST_ID number not null,\nConstraint fk_custid foreign key (cust_id) references TB_CUSTOMER (cust_id) ON DELETE CASCADE)\nPartition by reference (fk_custid);\nTable created.<\/pre>\n<p>We can see that both parent and child tables have been created:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, partitioning_type, ref_ptn_constraint_name\nfrom user_part_tables;\n\u00a0\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REF_PTN_CONSTRAINT_NAME\n-------------------- --------------------- --------------------------\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RANGE\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REFERENCE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FK_CUSTID<\/pre>\n<p>The partitions for the child table have been automatically created from the parent reference table.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, partition_name from user_tab_partitions;\n\u00a0\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\n------------------------------ ----------------------------------\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12<\/pre>\n<p>We can check if new interval partitions will automatically be created in the child table by creating a new customer on a non-existing range (Ex: 2014). This will generate a new interval partition in TB_CUSTOMER table.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2014','dd.mm.yyyy'));\nSQL&gt; commit;<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, partition_name from user_tab_partitions;\n\u00a0\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\n------------------------------ -----------------------------------------------\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SYS_P401\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12<\/pre>\n<p>Only one new partition &#8211; for TB_CUSTOMER &#8211; has been created. But when creating new records in TB_INVOICES for customers registered in 2014, a new partition will automatically be created in TB_INVOICE table.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; insert into TB_INVOICE values (1, to_date('21.04.2013','dd.mm.yyyy'),1);\nSQL&gt; commit;<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, partition_name from user_tab_partitions;\n\u00a0\nTABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PARTITION_NAME\n------------------------------ --------------------------------------------------\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_CUSTOMER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SYS_P401\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2011_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2012_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 P_2013_31_12\nTB_INVOICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SYS_P401<\/pre>\n<p>As a conclusion, we can see that interval partitioning is completely supported for reference partitioning: The partitions are created from the parent table at child table creation time and new required partitions are created automatically when the child table is filled.<\/p>\n<p>Coming soon in my next blog posting: &#8220;Oracle 12c: Partitioning enhancements Part II &#8211; Other improvements&#8221;, if you want to know more about partitioning enhancements with Oracle 12c.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples. TRUNCATE and EXCHANGE with the CASCADE option With Oracle 12c, it is now possible to use the CASCADE [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":2695,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[209,366,36],"type_dbi":[],"class_list":["post-2973","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-oracle-12c","tag-partitioning","tag-tablespaces"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning - dbi Blog<\/title>\n<meta name=\"description\" content=\"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning\" \/>\n<meta property=\"og:description\" content=\"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-05T07:35:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"406\" \/>\n\t<meta property=\"og:image:height\" content=\"100\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle Database 12c: Partitioning enhancements Part I &#8211; Reference partitioning\",\"datePublished\":\"2013-08-05T07:35:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\"},\"wordCount\":542,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\",\"keywords\":[\"Oracle 12c\",\"Partitioning\",\"Tablespaces\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\",\"name\":\"Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\",\"datePublished\":\"2013-08-05T07:35:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg\",\"width\":406,\"height\":100},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Database 12c: Partitioning enhancements Part I &#8211; Reference partitioning\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning - dbi Blog","description":"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning","og_description":"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2013-08-05T07:35:00+00:00","og_image":[{"width":406,"height":100,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg","type":"image\/jpeg"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle Database 12c: Partitioning enhancements Part I &#8211; Reference partitioning","datePublished":"2013-08-05T07:35:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/"},"wordCount":542,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg","keywords":["Oracle 12c","Partitioning","Tablespaces"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/","name":"Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg","datePublished":"2013-08-05T07:35:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_Oracle_Database_12c.jpg","width":406,"height":100},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-partitioning-enhancements-part-i-reference-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Database 12c: Partitioning enhancements Part I &#8211; Reference partitioning"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2973","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2973"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2973\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/2695"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2973"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2973"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2973"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2973"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}