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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

ALTER TABLE
   <tbl-name>
SPLIT PARTITION ... INTO... TABLESPACE <tblspace-name>.

 

ALTER TABLE
   my_table
SPLIT PARTITION
   my_table_part
AT (3)
INTO (PARTITION
    my_table_part_1,
PARTITION
    my_table_part_2);

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)
UPDATE GLOBAL INDEXES;

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)
UPDATE GLOBAL INDEXES;

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:

ALTER TABLE trains
  SPLIT PARTITION STATION5 AT ( '50-001' )
  INTO (
    PARTITION station5 TABLESPACE train009 (MINEXTENTS 2),
    PARTITION station9 TABLESPACE train010 )
  PARALLEL ( DEGREE 9 );

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational