Skip to Main Content
  • Questions
  • migrating data from non-partitioned to partitioned table with exchange then...?

Breadcrumb

Question and Answer

Maria Colgan

Thanks for the question, Torocsik.

Asked: February 23, 2017 - 9:43 am UTC

Last updated: February 25, 2017 - 1:16 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello,

I have to migrating data from one non-partitioned to table into a new range-list composite partitioned.
I started with exchange subpartition into P_DEFAULT_MODULES (wich holds all the literal values in subpartition key) without validation, then wanted to split that subpartition cause ora-14400: inserted key does not map to any partition.
I understand because P_DEFAULT's range partition is less than the splitting data's minimum range.
I tried update the partition keys to itselves after enabling row movement - it does not work, every data stays in P_DEFAULT_MODULES.
What am I missing? How should i make the new partitioned table's default partitions and subpartitions, where should I exchange the old data that I want to split into subpartitions?

Thanks!

and we said...

In order for your approach to work, the initial partitioned table you create must be defined so that it can legally contain all of the data you are about to exchange in. For example,

--
-- T is our unpartitioned table
--
SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects where object_id is not null;

Table created.


--
-- T1 is our partitioned table
--

SQL>
SQL> drop table t1 purge;

Table dropped.


SQL> create table t1 (
  2   OWNER                                                           VARCHAR2(128)
  3  , OBJECT_NAME                                                    VARCHAR2(128)
  4  , SUBOBJECT_NAME                                                 VARCHAR2(128)
  5  , OBJECT_ID                                                      NUMBER
  6  , DATA_OBJECT_ID                                                 NUMBER
  7  , OBJECT_TYPE                                                    VARCHAR2(23)
  8  , CREATED                                                        DATE
  9  , LAST_DDL_TIME                                                  DATE
 10  , TIMESTAMP                                                      VARCHAR2(19)
 11  , STATUS                                                         VARCHAR2(7)
 12  , TEMPORARY                                                      VARCHAR2(1)
 13  , GENERATED                                                      VARCHAR2(1)
 14  , SECONDARY                                                      VARCHAR2(1)
 15  , NAMESPACE                                                      NUMBER
 16  , EDITION_NAME                                                   VARCHAR2(128)
 17  , SHARING                                                        VARCHAR2(13)
 18  , EDITIONABLE                                                    VARCHAR2(1)
 19  , ORACLE_MAINTAINED                                              VARCHAR2(1)
 20  )
 21  partition by range ( object_Id )
 22  subpartition by list (object_type )
 23  subpartition template
 24  (subpartition s1 values (default) )
 25  (
 26    partition p1 values less than ( maxvalue )
 27  );

Table created.


T1's single subpartition can hold all of that data in table T without any problems. Thus we can exchange it in.

SQL>
SQL> alter table t1 exchange subpartition P1_S1 with table t;

Table altered.



At this point, we can begin to split the data at the subpartition or partition level to achieve the desired partitioning scheme.

SQL>
SQL> alter table t1 split subpartition P1_S1 into
  2   ( subpartition p1_s1a values ('TABLE','INDEX'),
  3     subpartition p1_s1b
  4   );

Table altered.

SQL>
SQL> alter table t1 split partition P1 into
  2   ( partition p1a values less than ( 50000 ),
  3     partition p1b
  4   );

Table altered.



Obviously this approach will result in a lot of data movement.

For this reason, I would recommend using DBMS_REDEFINITION, which would allow the process to be done more efficiently, and with minimal downtime. You can find an example of how to use DBMS_REDEFINITION to partition a table in 11g in the Oracle documentation http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#CJAEBFJA

In 12.2, this task would be a lot simpler as you can convert a table to a partitioned one with a single DDL statement (which can also be done online).

ALTER TABLE sales t 
PARTITION BY range (object_Id)
  (partition p1 values less than ( 50000 ),
   partition p2 values less than ( 100000 ),
   partition p3 values less than ( 150000 ),
   partition p4 values (DEFAULT))
UPDATE INDEXES ONLINE;




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.