In Oracle 18c+, Is it possible to use Table Flashback with a reference partitioned table?

Question

From the documentation, it's not very clear to me whether flashback is supported for reference partitioned tables:

Restrictions on Flashing Back Tables

This statement is subject to the following restrictions:

• Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

• The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; changing a column encryption key; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

But when I test it, it rather looks bad:

CREATE TABLE PARENT
(
    ID INT PRIMARY KEY
) PARTITION BY RANGE (ID)
(
    PARTITION P1 VALUES LESS THAN (MAXVALUE )
);

CREATE TABLE CHILD
(
    ID        INT PRIMARY KEY,
    PARENT_ID INT NOT NULL,
    CONSTRAINT FK_PARENT FOREIGN KEY (PARENT_ID) REFERENCES PARENT (ID)
) PARTITION BY REFERENCE (FK_PARENT);

ALTER TABLE CHILD
    ENABLE ROW MOVEMENT;

ALTER TABLE PARENT
    ENABLE ROW MOVEMENT;

FLASHBACK TABLE PARENT, CHILD TO RESTORE POINT BASE;

Fails for CHILD with:

ORA-08199 Flashback Table operation is not supported on this object.

So the answer seems to be an obvious "no", but then again, it's not so obvious from the documentation. Is there maybe another way I haven't considered?

Background: I would like to use Flashback to quickly reset all tables in my containerized Oracle DB during an automated system test.

Answer 1

Yep, it's unsupported; I'm working on getting the docs updated.

If you want to reset all tables, you can get around this using flashback database. This reverts the whole database back to the restore point.

Assuming you're using pluggable databases, connect to the root container as a sysdba user and run:

alter pluggable database <pdbname> close;
flashback pluggable database <pdbname> to restore point base;
alter pluggable database <pdbname> open resetlogs;