SQL & PL/SQL

Hi folks,

I have to partition two big tables (parent_unpart and child_unpart).

The first table parent is partitioned by range and subpartitioned by hash.
Here it is no problem to create a new partitioned table using "create table as select" (CTAS) for the parent-table.

So i can create and import in one step.


My child table should use reference partitioning by the foreign key. I would try to use CTAS again, but oracle gives me
ORA-00942.

The foreign key constraint shows

Here is my statement:

CREATE TABLE child_partitioned
(
child_parent_id
, child_data
-- Constraint für Foreign Key
, CONSTRAINT C_FK_CHILD_PARENT_ID FOREIGN KEY
(
child_parent_id
)
REFERENCES parent
(
parent_id
)
ON DELETE CASCADE ENABLE
)
PARTITION BY REFERENCE (C_FK_CHILD_PARENT_ID)
as select * from child;

The foreign-key-constraints points at the primary key of the parent_table.
Parent key and foreign key are not null.

Doing it in 2 steps (1. create table and 2. insert as select)
Do you have any ideas?

I would like to test if CTAS is using less undo space than inserting the data on 2nd step.
Thanks,
Andreas