Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson
Advanced Oracle Tips and Tricks for the Oracle DBA

Debugging Oracle 11g Applications
by Ben


Reference Partitioning in Oracle 11g

Oracle 11g provides an additional new type of partitioning called reference partitioning which allows for partitioning by using the parent table referenced by a foreign key constraint. It allows for use of existing parent-child relationship based partitioning enforced by active primary key and foreign key constraints. The partition by reference clause is used to create reference partitions as shown in the following example. First, we will need to create a table that is partitioned to server as the parent table which will be referenced by the second table that is to be reference partitioned.

SQL> create table parent_emp(
  2  empno      number  primary key,
  3  job        varchar2(20),
  4  sal        number(7,2),
  5  deptno     number(2)
  6  )
  7  partition by list(job)
  8  ( partition p_job_dba values ('DBA'),
  9    partition p_job_mgr values ('MGR'),
 10    partition p_job_vp  values ('VP')
 11  );

Table created.

SQL> create table reference_emp
  2  (
  3  ename      varchar2(10),
  4  emp_id     number  primary key,
  5  empno      not null,
  6  constraint fk_empno foreign key(empno)
  7     references parent_emp(empno)
  8  )
  9  partition by reference (fk_empno)
 10  /

Table created.


Figure 1.16: Reference Partition on new child table in Oracle 11g


Now that we have our reference partition created on the child table, we can query both tables to determine the partition information from the user_part_tables view.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
  2  from user_part_tables
  3  where table_name='PARENT_EMP'
  4  or
  5  table_name='REFERENCE_EMP';


------------------------------ --------- ------------------------------
PARENT_EMP                     LIST

Here we see that the
REFERENCE_EMP child table has been reference partitioned using the foreign key constraint FK_EMPNO. Next we can view the high values for the partitions in both tables with the following query.

SQL> select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name='PARENT_EMP'
  4  or
  5  table_name='REFERENCE_EMP'
  6  /

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
PARENT_EMP                     P_JOB_DBA

PARENT_EMP                     P_JOB_MGR

PARENT_EMP                     P_JOB_VP

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
REFERENCE_EMP                  P_JOB_DBA
REFERENCE_EMP                  P_JOB_MGR
REFERENCE_EMP                  P_JOB_VP

6 rows selected.


Figure 1.17: High value positions for Reference Partition in Oracle 11g


Reference partitioning is useful in that partition operations against the parent table are duplicated to the child table thus reducing errors when manually performing maintenance against both tables. Before Oracle 11g, you would have to manually allocate storage for the partition key on both the parent and child table. Now with Oracle 11g, less storage is required and easier maintenance is provided.

New Feature for Oracle 11g: Active Data Guard

Oracle 11g introduces several new enhancements to improve the operation and maintenance of complex Data Guard standby environments for improved high availability. The many new features with Oracle 11g for Data Guard include the following:

  • Real time query standby database

  • Redo log compression

  • Logical standby enhancements

  • Data Guard Broker enhancements

  • Fast start failover improvements

  • Integration for RMAN with Data Guard

  • Snapshot standby database

Further details on the steps for how to configure and setup a physical standby and logical standby database using the new features for Oracle 11g with Data Guard are available in the Oracle 11g Data Guard Concepts and Administration reference guide available from the Oracle OTN site located online at



Oracle Consulting


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.