Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









System Partitioning in 11g

Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

System partitioning allows application level control for partitioning a table or index.  This partitioning option offers the same benefits as other methods of partitioning.   However, the partitions are completely controlled by the application without partition keys or boundaries.  This option should be used carefully, and all references to a system partitioned table must include extended partition syntax to specify a partition.  Also, for inserts into a system partitioned table, the partition must be specified to avoid receiving an error.  For all other operations, omitting the partition will cause all partitions to be scanned. 

Below is an example of a table that might benefit from system partitioning is a large lookup table owned by the development group.  Consider the following table:

create table
misc_lookups (
   lookup_name varchar2(30),
   lookup_meaning varchar2(30)

If this table is very large and there is no obvious way to partition it, system partitioning might be a practical option.  This option would be suitable if the table was primarily used in automated processes, such as PL/SQL packages.  In either case, the developers would need to determine the rules for deciding how to choose which partitions to use for all operations on this table.  In order to enable system partitioning with three partitions in different tablespaces, the table is created using the following syntax:

create table
misc_lookups (
   lookup_name varchar2(30),
   lookup_meaning varchar2(30)
partition by system
   partition misc_lookups_p0 tablespace tablespaceA,
   partition misc_lookups_p1 tablespace tablespaceB,
   partition misc_lookups_p2 tablespace tablespaceC

It is the developers responsibility to use the extended partition syntax to specify the partition when using the table.  This is a requirement when inserting a row to a system partitioned table since the database has no means for choosing which partition to store new rows.  Failing to specify a partition when inserting a row will raise an error:

SQL> insert into misc_lookups values ('test', 'test meaning')
  2  /
insert into misc_lookups values ('test', 'test meaning')
ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method 

Therefore, a partition must be specified when inserting into a system partitioned table such as:

SQL> insert into misc_lookups partition (misc_lookups_p0) values ('test', 'test meaning')
  2  / 

1 row created.

When selecting, updating, or deleting data from a system partitioned object, the best practice is to always specify the partition to avoid scanning all partitions.  The use of system partitioning should be viewed with scrutiny by a DBA since it transfers responsibility of optimal use of partitions away from the DBA and to the developers and applications.  Nevertheless, the introduction of this new feature helps to extend the use of partitioning for any business situation. 


This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.