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 








alter table split partition Tips

Oracle Database Tips by Donald BurlesonNovember 12, 2015

Question:  I have a question about the use of alter table split partition.  Here is what we did trying to use the alter table split partition command:

  1. We created table with single partition on tablespace.

  2. We created local index on this single partition on tablespace.

After splitting the partition, we found that both data partitions and index partitions are on the same tablespace specified for the following alter table split partition syntax:



AT (3)

The problem here is after splitting the table with alter table <tbl-name> split partition, Oracle also splits the local indexes accordingly but moves the indexes, originally defined from different tablespace, to the same tablespace of the underlying table. The steps I did were:

1. Create tables with single partition in one (data) tablespace

2. Create local indexes based on the single partition (above) in another (index) tablespace

3. Perform partition splitting on single partition to create multiple partitions using the
alter table <tbl-name> split partition command.

After splitting the partition, Oracle automatically splits the local indexes accordingly as well but have both data and indexes in the originally defined tablespace.

We have reviewed the Oracle documentation on the use of alter table split partition in an attempt to resolve the issue.  With regard to the local indexes for the new partition and for the existing partition from which rows were redistributed, these are marked UNUSABLE and must be rebuilt.

Thus, after using the alter table split partition process, the new index partitions get their attributes from the original partition.  Unless otherwise specified, new index partitions are stored in the default tablespace of the partition being split.  If the original index partition has not got a default tablespace, the tablespace of the new underlying table partitions will be used.

Dr, Hall has these examples of using the alter table split partition syntax:

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Is there anyway to achieve the same partition splitting as seen in the alter table <tbl-name> split partition command, but still have the data and indexes on a separate tablespaces?

Answer:  Oracle says that unless you specify update global indexes, any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt after using the alter table <tbl-name> split partition command.

Hence, you can put back the index, after issuing the alter table <tbl-name> split partition command, with an index rebuild command:

alter index iii rebuild tablespace ttt;

Most DBAs will place indexes into a separate tablespace for easier management and reporting rather than for improved performance.

The SPLIT PARTITION option divides a partition into two partitions, each with a new segment, new physical attributes, and new initial extents. The segment associated with the old partition is discarded.

This statement also performs a matching split on the corresponding partition in each local index defined on the table. The index partitions are split even if they are marked unusable.

With the exception of the TABLESPACE attribute, the physical attributes of the LOCAL index partition being split are used for both new index partitions. If the parent LOCAL index lacks a default TABLESPACE attribute, new LOCAL index partitions will reside in the same tablespace as the corresponding newly created partitions of the underlying table.

If you do not specify physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE) for the new partitions, the current values of the partition being split are used as the default values for both partitions.

If partition_name is not empty, SPLIT PARTITION marks all affected index partitions as unusable. This includes all global index partitions, as well as the local index partitions that result from the split.

The PARALLEL clause on SPLIT PARTITION does not change the default PARALLEL attributes of table.

The following example splits the old partition station5, thereby creating a new partition for station9:

  INTO (
    PARTITION station5 TABLESPACE train009 (MINEXTENTS 2),
    PARTITION station9 TABLESPACE train010 )

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



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.