-1

I have a parent and child tables. Parent table has a partition and a sub partition. Child table needs to have reference partition based on parent partition.

Problem: If I create a new child table and creates a reference partition based on foreign key from the parent, child table gets created without any problem.

But if I have an existing non-partitioned child table and wants to add a partition to this child table based on reference partition, Oracle throws "ORA-00902: invalid datatype error" error. How can I partition existing table using reference partition?

Here is the example:

PARENT TABLE

CREATE TABLE parent_tab (
  id           NUMBER NOT NULL,
  code         VARCHAR2(10) NOT NULL,
  description  VARCHAR2(50),
  created_date TIMESTAMP(6),
  CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
SUBPARTITION BY LIST (description)
   SUBPARTITION template 
   (
      SUBPARTITION i1 VALUES ('complete'),
      SUBPARTITION i2 VALUES ('execution'),
      SUBPARTITION ix VALUES (DEFAULT)
   )
(
   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);

Create a new CHILD table with reference partition

CREATE TABLE child_tab (
  id             NUMBER NOT NULL,
  parent_tab_id  NUMBER NOT NULL,
  code           VARCHAR2(10),
  description    VARCHAR2(50),
  created_date   DATE,
  CONSTRAINT child_tab_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
                               REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);

Works great

table CHILD_TAB created.

But if I have an existing child table and wants to add reference partition it fails.

CREATE TABLE child_tab (
  id             NUMBER NOT NULL,
  parent_tab_id  NUMBER NOT NULL,
  code           VARCHAR2(10),
  description    VARCHAR2(50),
  created_date   DATE,
  CONSTRAINT child_tab_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
                               REFERENCES parent_tab (id)
);

alter table child_tab add partition  BY REFERENCE (child_parent_tab_fk);

Result:

alter table child_tab add partition  BY REFERENCE (child_parent_tab_fk)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:

migrated from stackoverflow.com May 19 '15 at 21:51

This question came from our site for professional and enthusiast programmers.

1

It looks like a non-partitioned table can not be partitioned using Alter table add partition command.

For the "alter table add partition" command to work, table should already have at least 1 partition. In other words, the table should be a partitioned table already.

That is the reason, create Table SQL given above with partition parameters works and alter partition SQL did not.

To partition a non-partition table I see following 3 methods:

Method 1: (Not Recommended) Create cloned partitioned table and replicate data

This is a crude way of creating a partitioned table.

Here is the non partitioned table example:

CREATE TABLE CUSTOMER
  ( CUSTOMERID NUMBER(10,0) NOT NULL ENABLE PRIMARY KEY,
    FIRSTNAME VARCHAR2(20) NOT NULL ENABLE,
    LASTNAME VARCHAR2(30) NOT NULL ENABLE,
    PHONE CHAR(10) NOT NULL ENABLE,
    ZIP CHAR(5) NOT NULL ENABLE
  )
  1. Create a duplicate partitioned table with exact same table structure.

    CREATE TABLE customer_temp 
    partition BY hash
    (zip) partitions 8 AS
    SELECT * FROM customer WHERE 1=0;
    
  2. Copy data from original table to cloned table

    insert into customer_temp select * from customer;
    
  3. Drop the original table with constraints

    DROP TABLE customer CASCADE CONSTRAINTS PURGE; 
    
  4. Rename the cloned table to original table

    ALTER TABLE customer_temp RENAME TO customer;
    
  5. Reconstruct all the constraints on the newly renamed table.

Method 2: DBMS_REDEFINITION package (Oracle 9i+)

This method creates a snapshot of the target table and applies all the changes to the table after the table has been reorganized with the "Create table as select" command.

The biggest advantage of this method over method 1 is table partitioning can be done without DB outage and without manually copying data from original table to cloned table. Following are the steps in the process detailed here.

  1. Verify that the table is a candidate for online redefinition
  2. Create an interim table
  3. Enable parallel DML operations
  4. Start the redefinition process
  5. Copy dependent objects
  6. Check for any errors
  7. Synchronize the interim table (optional)
  8. Complete the redefinition
  9. Drop the interim table

There are few restrictions on using this package. They are outlined here

Method 3: EXCHANGE PARTITION package

Exchange partitioning is another way to partition a non partitioned table by creating first a single partitioned table and then splitting the partitions repeatedly until all the required partitions are created.

This method has 3 steps

  1. Create a cloned MAXVALUE partitioned table.
  2. Run exchange partition command
  3. Run split partitioned commands

Details of this method can be found at location the following link

oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php

(Since I do not have enough reputations, not able post this as a link)

The draw backs of this method is

This method starts by creating one-partition table. That partition needs to be 'split', and this split can create only two partitions out of one at a time (unless DB is oracle 12c), so multiple splits needs to be run and each split is full scan on the partition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.