Oracle comment archiver une table partitionnée de référence

Question

J'essaie de déplacer les données d'une table parent partitionnée par intervalles et d'une table enfant partitionnée par référence, dans la structure de table d'archive correspondante.

Et à ma grande surprise, cette tâche apparemment simple est hors de ma portée...

J'ai lu sur déplacement d'une table partitionnée vers une archive mais il ne dit pas comment déplacer les tables avec des clés étrangères.

J'ai également lu des informations sur déplacement des tables partitionnées de référence Mais cette solution ne fonctionne pas pour moi, je reçois cette erreur après la première déclaration :

ORA-02266 : clés uniques/primaires dans la table référencée par des clés étrangères activées

J'ai donc essayé ma propre approche. Elle me donne une erreur encore plus grave :

ORA-00600 : code d'erreur interne, arguments : [kkpamRefGet : index], [], [], [], [], [], [], [], [], [], [], [], [].

Vous pouvez reproduire ma dernière tentative comme suit :

/*
DROP TABLE CHILD_TABLE
/
DROP TABLE CHILD_TABLE_ARCHIVE
/
DROP TABLE CHILD_TABLE_TMP
/
DROP TABLE PARENT_TABLE
/
DROP TABLE PARENT_TABLE_ARCHIVE
/
DROP TABLE PARENT_TABLE_TMP
/
*/
-- SAMPLE STRUCTURE
CREATE TABLE PARENT_TABLE
(
    PARTITION_DATE DATE NOT NULL,
    PK NUMBER NOT NULL
)
PARTITION BY RANGE (PARTITION_DATE)
INTERVAL( NUMTOYMINTERVAL(1, 'YEAR'))
(  
PARTITION PARTITION_1 VALUES LESS THAN (TIMESTAMP' 2017-01-01 00:00:00'),
PARTITION PARTITION_2 VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00')
)
/
ALTER TABLE PARENT_TABLE ADD (CONSTRAINT PK_PARENT_TABLE PRIMARY KEY (PK) ENABLE VALIDATE)
/
CREATE TABLE CHILD_TABLE
(
    PK NUMBER NOT NULL,
    FK_PARENT NUMBER NOT NULL,
    CONSTRAINT FK_CHILD_TABLE
    FOREIGN KEY (FK_PARENT) 
    REFERENCES PARENT_TABLE (PK)
    ON DELETE CASCADE
    ENABLE VALIDATE
)
PARTITION BY REFERENCE (FK_CHILD_TABLE)
/
CREATE TABLE PARENT_TABLE_ARCHIVE
(
    PARTITION_DATE DATE NOT NULL,
    PK NUMBER NOT NULL
)
PARTITION BY RANGE (PARTITION_DATE)
INTERVAL( NUMTOYMINTERVAL(1, 'YEAR'))
(  
PARTITION PARTITION_1 VALUES LESS THAN (TIMESTAMP' 2017-01-01 00:00:00'),
PARTITION PARTITION_2 VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00')
)
/
ALTER TABLE PARENT_TABLE_ARCHIVE ADD (CONSTRAINT PK_PARENT_TABLE_ARCHIVE PRIMARY KEY (PK) ENABLE VALIDATE)
/
CREATE TABLE CHILD_TABLE_ARCHIVE
(
    PK NUMBER NOT NULL,
    FK_PARENT NUMBER NOT NULL,
    CONSTRAINT FK_CHILD_TABLE_ARCHIVE
    FOREIGN KEY (FK_PARENT) 
    REFERENCES PARENT_TABLE_ARCHIVE (PK)
    ON DELETE CASCADE
    ENABLE VALIDATE
)
PARTITION BY REFERENCE (FK_CHILD_TABLE_ARCHIVE)
/
CREATE TABLE PARENT_TABLE_TMP AS SELECT * FROM PARENT_TABLE WHERE 1 = 2
/
CREATE TABLE CHILD_TABLE_TMP AS SELECT * FROM CHILD_TABLE WHERE 1 = 2
/
-- SAMPLE DATA
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2017-01-01','YYYY-MM-DD'),1)
/
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2018-01-01','YYYY-MM-DD'),2)
/
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2019-01-01','YYYY-MM-DD'),3)
/
INSERT INTO CHILD_TABLE VALUES (1,1)
/
INSERT INTO CHILD_TABLE VALUES (2,2)
/
INSERT INTO CHILD_TABLE VALUES (3,3)
/
-- My last attempt to do this
ALTER TABLE CHILD_TABLE
EXCHANGE PARTITION PARTITION_2
WITH TABLE CHILD_TABLE_TMP
/
ALTER TABLE PARENT_TABLE
EXCHANGE PARTITION FOR (TO_DATE('2017-01-01','YYYY-MM-DD'))
WITH TABLE PARENT_TABLE_TMP
/
--Without this constraint I am geting: ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
ALTER TABLE PARENT_TABLE_TMP ADD (CONSTRAINT PK_PARENT_TABLE_TMP PRIMARY KEY (PK) ENABLE VALIDATE)
/
ALTER TABLE PARENT_TABLE_ARCHIVE
EXCHANGE PARTITION FOR (TO_DATE('2017-01-01','YYYY-MM-DD'))
WITH TABLE PARENT_TABLE_TMP
/
--Without this constraint I am geting:  ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION
ALTER TABLE CHILD_TABLE_TMP ADD
CONSTRAINT FK_CHILD_TABLE_TMP
FOREIGN KEY (FK_PARENT) 
REFERENCES PARENT_TABLE_ARCHIVE (PK)
ON DELETE CASCADE
ENABLE VALIDATE
/
-- And here is something starange...
ALTER TABLE CHILD_TABLE_ARCHIVE
EXCHANGE PARTITION PARTITION_2
WITH TABLE CHILD_TABLE_TMP
/

Actuellement, je n'ai plus d'idées sur la manière de procéder, et je commence à me demander si je devrais même essayer de déplacer cette structure vers l'archive... Elle est divisée par dates, alors puis-je la laisser grandir ?

J'apprécierais toute aide dans ce domaine :)

UPDATE :

Tout comme Francisco suggéré, CASCADE a aidé avec mon exemple de code. Voici mon exemple de code en état de marche.

Mais dans mon scénario réel, il y a plus d'un enfant dans le tableau des parents. Ainsi, lorsque j'essaie d'utiliser l'option CASCADe, j'obtiens ORA-14706 . La solution proposée est de ne pas utiliser l'option CASCADE...

Je ne peux donc toujours pas archiver mes tableaux et j'ai vraiment besoin d'aide.

DROP TABLE CHILD_TABLE
/
DROP TABLE CHILD_TABLE_ARCHIVE
/
DROP TABLE CHILD_TABLE_TMP
/
DROP TABLE PARENT_TABLE
/
DROP TABLE PARENT_TABLE_ARCHIVE
/
DROP TABLE PARENT_TABLE_TMP
/
-- SAMPLE STRUCTURE
CREATE TABLE PARENT_TABLE
(
    PARTITION_DATE DATE NOT NULL,
    PK NUMBER NOT NULL
)
PARTITION BY RANGE (PARTITION_DATE)
INTERVAL( NUMTOYMINTERVAL(1, 'YEAR'))
(  
PARTITION PARTITION_1 VALUES LESS THAN (TIMESTAMP' 2017-01-01 00:00:00'),
PARTITION PARTITION_2 VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00')
)
/
ALTER TABLE PARENT_TABLE ADD (CONSTRAINT PK_PARENT_TABLE PRIMARY KEY (PK) ENABLE VALIDATE)
/
CREATE TABLE CHILD_TABLE
(
    PK NUMBER NOT NULL,
    FK_PARENT NUMBER NOT NULL,
    CONSTRAINT FK_CHILD_TABLE
    FOREIGN KEY (FK_PARENT) 
    REFERENCES PARENT_TABLE (PK)
    ON DELETE CASCADE
    ENABLE VALIDATE
)
PARTITION BY REFERENCE (FK_CHILD_TABLE)
/
CREATE TABLE PARENT_TABLE_ARCHIVE
(
    PARTITION_DATE DATE NOT NULL,
    PK NUMBER NOT NULL
)
PARTITION BY RANGE (PARTITION_DATE)
INTERVAL( NUMTOYMINTERVAL(1, 'YEAR'))
(  
PARTITION PARTITION_1 VALUES LESS THAN (TIMESTAMP' 2017-01-01 00:00:00'),
PARTITION PARTITION_2 VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00')
)
/
ALTER TABLE PARENT_TABLE_ARCHIVE ADD (CONSTRAINT PK_PARENT_TABLE_ARCHIVE PRIMARY KEY (PK) ENABLE VALIDATE)
/
CREATE TABLE CHILD_TABLE_ARCHIVE
(
    PK NUMBER NOT NULL,
    FK_PARENT NUMBER NOT NULL,
    CONSTRAINT FK_CHILD_TABLE_ARCHIVE
    FOREIGN KEY (FK_PARENT) 
    REFERENCES PARENT_TABLE_ARCHIVE (PK)
    ON DELETE CASCADE
    ENABLE VALIDATE
)
PARTITION BY REFERENCE (FK_CHILD_TABLE_ARCHIVE)
/
CREATE TABLE PARENT_TABLE_TMP AS SELECT * FROM PARENT_TABLE WHERE 1 = 2
/
CREATE TABLE CHILD_TABLE_TMP AS SELECT * FROM CHILD_TABLE WHERE 1 = 2
/
ALTER TABLE PARENT_TABLE_TMP ADD (CONSTRAINT PK_PARENT_TABLE_TMP PRIMARY KEY (PK) ENABLE VALIDATE)
/
ALTER TABLE CHILD_TABLE_TMP ADD
CONSTRAINT FK_CHILD_TABLE_TMP
FOREIGN KEY (FK_PARENT) 
REFERENCES PARENT_TABLE_TMP (PK)
ENABLE VALIDATE
/
-- SAMPLE DATA
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2017-01-01','YYYY-MM-DD'),1)
/
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2018-01-01','YYYY-MM-DD'),2)
/
INSERT INTO PARENT_TABLE VALUES (TO_DATE('2019-01-01','YYYY-MM-DD'),3)
/
INSERT INTO CHILD_TABLE VALUES (1,1)
/
INSERT INTO CHILD_TABLE VALUES (2,2)
/
INSERT INTO CHILD_TABLE VALUES (3,3)
/
ALTER TABLE PARENT_TABLE
EXCHANGE PARTITION FOR (TO_DATE('2017-01-01','YYYY-MM-DD'))
WITH TABLE PARENT_TABLE_TMP
CASCADE UPDATE INDEXES
/
ALTER TABLE PARENT_TABLE_ARCHIVE
EXCHANGE PARTITION FOR (TO_DATE('2017-01-01','YYYY-MM-DD'))
WITH TABLE PARENT_TABLE_TMP
CASCADE UPDATE INDEXES
/

Answer 1

-----creating reference partitioning on existing tables having parent child relationship

----------------------------------------consider the following parent and child table---------------------------------------------------
--create the parent table:

create table t1(
  id int,
  action_date   varchar(8),
  constraint t1_pk primary key (id)
);

--create the child table table:
create table t2(
  id int,
  id_t1,
  constraint t2_pk primary key (id),
  constraint t2_fk1 foreign key (id_t1) references t1(id)
);

---insert sample data into parent
insert into t1 values(1,'20170801');
insert into t1 values(2,'20170901');
insert into t1 values(3,'20171001');

---insert sample data into child
insert into t2 values(1,1);
insert into t2 values(2,1);
insert into t2 values(3,1);
insert into t2 values(4,2);
insert into t2 values(5,1);
insert into t2 values(6,2);
insert into t2 values(7,3);

---------------------------------------------------------creating intermediate table for partitioning-----------------------------------------------------------------
-----------------parent table-------------------------
create table t1_part(
  id int,
  action_date   varchar(8),
  constraint t1_part_pk primary key (id)
) PARTITION BY RANGE (action_date)
    (
        PARTITION t1_part_old VALUES LESS THAN ('20171101'),
        PARTITION t1_part_201711 VALUES LESS THAN ('20171201'),
        PARTITION t1_part_201712 VALUES LESS THAN ('20180101'),
    PARTITION t1_part_def VALUES LESS THAN ('99999999')
  );

-------------------dropping constraints-----------------
ALTER TABLE t2 DROP constraint t2_fk1;  
ALTER TABLE t1 DROP constraint t1_pk;
ALTER TABLE t1_part DROP constraint t1_part_pk;

desc t1;
desc t1_part;

-------------------------switch parent data to partitioned table from unpartitionedtable-------------------------------
ALTER TABLE t1_part EXCHANGE PARTITION t1_part_old WITH TABLE t1 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t1_part EXCHANGE PARTITION t1_part_201711 WITH TABLE t1 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t1_part EXCHANGE PARTITION t1_part_201712 WITH TABLE t1 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t1_part EXCHANGE PARTITION t1_part_def WITH TABLE t1 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

--------------------------------update stats----------------------------------
EXEC DBMS_STATS.gather_table_stats('SYSTEM', 'T1', cascade => TRUE);

ALTER TABLE t1_part add constraint t1_part_pk primary key (id);
--select * from t1_part;

drop table t1;
RENAME t1_part TO t1;
ALTER TABLE t1 RENAME CONSTRAINT t1_part_pk TO t1_pk;
ALTER TABLE t2 add constraint t2_fk1 foreign key (id_t1) references t1(id);  

--------------------------------update stats----------------------------------
EXEC DBMS_STATS.gather_table_stats('SYSTEM', 'T1', cascade => TRUE);

----------------------create partitioned child table---------------------------------------------
create table t2_part(
  id int,
  id_t1 not null,
  constraint t2_part_pk primary key (id),
  constraint t2_part_fk1 foreign key (id_t1) references t1(id)
) partition by reference (t2_part_fk1);

alter table t2 modify (id_t1 int not null);

desc t2;
desc t2_part

-------------------------switch child data to partitioned table from unpartitionedtable-------------------------------
ALTER TABLE t2_part EXCHANGE PARTITION t1_part_old WITH TABLE t2 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t2_part EXCHANGE PARTITION t1_part_201711 WITH TABLE t2 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t2_part EXCHANGE PARTITION t1_part_201712 WITH TABLE t2 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE t2_part EXCHANGE PARTITION t1_part_def WITH TABLE t2 WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

--select * from t2_part;
drop table t2;
RENAME t2_part TO t2;
ALTER TABLE t2 RENAME CONSTRAINT t2_part_pk TO t2_pk;
ALTER TABLE t2 RENAME CONSTRAINT t2_part_fk1 TO t2_fk1;

--------------------------------update stats----------------------------------
EXEC DBMS_STATS.gather_table_stats('SYSTEM', 'T2', cascade => TRUE);

-----------------------------------check partition info---------------------------------------------
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('T1','T2');

desc t1;
desc t2;
-----------------add new parent partition, child partiion will automatically get created thorugh reference paritioning through foreign key t2_part_fk1
ALTER TABLE T1 SPLIT PARTITION t1_part_old AT ('20170701') INTO (PARTITION t1_part_old, PARTITION t1_part_201707) UPDATE GLOBAL INDEXES;
ALTER TABLE T1 SPLIT PARTITION t1_part_201707 AT ('20170801') INTO (PARTITION t1_part_201707, PARTITION t1_part_201708) UPDATE GLOBAL INDEXES;
ALTER TABLE T1 SPLIT PARTITION t1_part_201708 AT ('20170901') INTO (PARTITION t1_part_201708, PARTITION t1_part_201709) UPDATE GLOBAL INDEXES;
ALTER TABLE T1 SPLIT PARTITION t1_part_201709 AT ('20171001') INTO (PARTITION t1_part_201709, PARTITION t1_part_201710) UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats('SYSTEM', 'T1', cascade => TRUE);

--------------------validating the partitioned data--------------------

select count(*) from T1 partition (T1_PART_OLD);
select * from T1 partition (T1_PART_201708);
select count(*) from T1 partition (T1_PART_201709);
select count(*) from T1 partition (T1_PART_201710);
select count(*) from T1 partition (T1_PART_201711);

select * from T2;
select * from T1;

select count(*) from T2 partition (T1_PART_OLD);
select * from T2 partition (T1_PART_201708);
select count(*) from T2 partition (T1_PART_201709);
select count(*) from T2 partition (T1_PART_201710);
select count(*) from T2 partition (T1_PART_201711);

-------truncate the partition T1_PART_201708 (refering the parent partition) and validate the data
alter table t2 truncate partition T1_PART_201708;
select * from t2;       ----------------- data from 20170801 to 20170831 will be truncated from child

-------truncate the parent record
alter table t1 drop partition T1_PART_201708;
select * from t1;