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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

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

Last updated: September 14, 2023 - 1:01 pm UTC

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 Chris 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.

Rating

  (1 rating)

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

Comments

Is it possible to do the same in Oracle 19c?

A reader, September 13, 2023 - 3:29 pm UTC

Is it possible separate out child table from referenced partitioned and make it as independent table in oracle 19c version?
Chris Saxon
September 14, 2023 - 1:01 pm UTC

I'm unsure what you're trying to do here - please clarify.

More to Explore

Administration

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