Skip to Main Content
  • Questions
  • Drop Foreign Key from Referenced-Partitioned Child table

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 11, 2020 - 1:36 pm UTC

Answered by: Chris Saxon - Last updated: September 11, 2020 - 4:48 pm UTC

Category: Database Development - Version: 12.1.0.1

Viewed 1000+ times

You Asked

Hi,

Could you please have a look at below table structure

drop table child purge
/

drop table parent purge
/

create table parent (id number(11,0)
                    ,dt date
                    ,constraint pk_parent primary key (id)
)
partition by range (dt) 
(
partition pytr_hist_p_jan_2018  values less than (to_date(' 2018-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')),
partition "pytr_hist_p_feb_2018"  values less than (to_date(' 2018-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')),
partition "pytr_hist_p_maxvalue"  values less than (maxvalue)
)
/


create table child (id number(11,0)
                    ,name varchar2(20 char)
                    ,constraint pk_child primary key (id)
                    ,constraint fk_parent_id foreign key (id) references parent (id)
)
partition by reference (fk_parent_id)
/


I need to separate out child table from referenced partitioned and make it as independent table.
I tried dropping the constraint, but due to reference-partition it not getting completed.
   
alter table child drop constraint fk_parent_id
/


Getting error as
   
ORA-14650: operation not supported for reference-partitioned tables
14650. 00000 -  "operation not supported for reference-partitioned tables"


On production environment, child table size if around 40 GB, so could you please suggest any alternative how can I detach child table from parent.

and we said...

You can't drop the foreign key used by reference partitioning.

If you want to remove partitioning, you'll have to re-create the table. Sadly you can't use dbms_redefinition to do this:

exec dbms_redefinition.can_redef_table ( user, 'CHILD' );

ORA-23549: table "CHRIS"."CHILD" involved in reference partitioning


I'd start by trying create-table-as-select:

create table child_new as 
  select * from child;


Copy the constraints, indexes, etc. from the old table to the new, then switch them over.

You'll likely have to take an outage to do this.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database