FLASHBACK TABLE TO BEFORE DROP and referential partitioning

Referential partitioning gives the advantage of automated cross-table partitioning support. It’s based on referential constraint to map the parent partition structure to the child one.
Also Oracle provides the useful option FLASHBACK TABLE … TO BEFORE DROP. But this feature has the essential restriction what makes it unusable for child tables of referential partitioning schema.

SQL> CREATE TABLE PARENT_TAB (
  2  PARENT_ID NUMBER NOT NULL,
  3  PARENT_NAME VARCHAR2 (30) NOT NULL,
  4  LOCAL_TRANSACTION_TIME TIMESTAMP NOT NULL,
  5  CONSTRAINT PK_PARENT PRIMARY KEY (PARENT_ID))
  6  PARTITION BY RANGE (LOCAL_TRANSACTION_TIME) (
  7  PARTITION P_20090206 VALUES LESS THAN (TIMESTAMP' 2009-02-07 00:00:00'),
  8  PARTITION P_20090207 VALUES LESS THAN (TIMESTAMP' 2009-02-08 00:00:00'),
  9  PARTITION P_20090209 VALUES LESS THAN (TIMESTAMP' 2009-02-09 00:00:00'))
 10  ENABLE ROW MOVEMENT;

Table created.


SQL> CREATE TABLE CHILD_TAB
  2  (
  3  CHILD_ID NUMBER,
  4  PID NUMBER NOT NULL,
  5  CLASSIFICATION NUMBER,
  6  CONSTRAINT PK_CHILD PRIMARY KEY (CHILD_ID),
  7  CONSTRAINT FK_CHILD_TO_PARENT FOREIGN KEY (PID) REFERENCES PARENT_TAB (PARENT_ID)
  8  )
  9  PARTITION BY REFERENCE (FK_CHILD_TO_PARENT)
 10  ENABLE ROW MOVEMENT;

Table created.

SQL> INSERT INTO PARENT_TAB
  2  VALUES(1,'PARENT X', TIMESTAMP' 2009-02-08 00:00:00')
  3  /

1 row created.

SQL> INSERT INTO CHILD_TAB
  2  VALUES(1,1,0)
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT partition_name FROM user_tab_partitions
  2  WHERE table_name = 'CHILD_TAB'
  3  /

PARTITION_NAME                                                                  
------------------------------                                                  
P_20090206                                                                      
P_20090207                                                                      
P_20090209                                                                      

SQL> drop table child_tab;

Table dropped.

SQL> flashback table child_tab to before drop;
flashback table child_tab to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN 


SQL> create table t (x int);

Table created.

SQL> drop table t;

Table dropped.

SQL> flashback table t to before drop;

Flashback complete.

As we can see the child table even was not placed into recyclebin.
Be care🙂

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s