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