闪回删除的表

创建TEST表：

Create table test as select * from user_objects;

 

创建TEST_BAK表：

Create table test_bak as select * from test;

 

创建procedure：

createorreplaceprocedurepro_test

is

id_cntnumber;

begin

selectcount(object_id)intoid_cntfromtest;

dbms_output.put_line('Object_id:'||id_cnt);

end;

 

创建trigger：

 

CREATE OR REPLACE TRIGGER "HAO"."TRG_TEST"                                   

after insert or delete on TEST                                                 

for each row                                                                   

begin                                                                          

dbms_output.put_line('modified!');                                             

exception                                                                      

when others then                                                               

dbms_output.put_line(sqlerrm);                                                 

end;                     

/

 

创建index：

Create index idx_test_object_id on test(object_id);

 

创建约束：

主键：

alter table test_bak add constraint pk_test_bak primary key (object_id);

外键：

alter table test add constraint fk_test foreign key (object_id) references test_bak(object_id);

 

 

 

 

 

 

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

PRO_TEST                      PROCEDURE          VALID

IDX_TEST_OBJECT_ID            INDEX              VALID

PK_TEST_BAK                   INDEX              VALID

TRG_TEST                      TRIGGER            VALID

 

SQL> select constraint_name,constraint_type,status from user_constraints;

 

CONSTRAINT_NAME               C STATUS

------------------------------ - --------

FK_TEST                       R ENABLED

PK_TEST_BAK                   P ENABLED

 

SQL> drop table test;

 

表已删除。

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST_BAK                      TABLE              VALID

BIN$qdWcERzdS+yMK7Vqv9E1UA==$0 TRIGGER            INVALID

PRO_TEST                      PROCEDURE          INVALID

BIN$v6zc94PYQCON0EGJ9tMvQQ==$0 TABLE              VALID

PK_TEST_BAK                   INDEX              VALID

BIN$MEmOkWEiQ8GnifY4O+9Y8g==$0 INDEX              VALID

 

已选择6行。

 

SQL> select constraint_name,constraint_type,status from user_constraints;

 

CONSTRAINT_NAME               C STATUS

------------------------------ - --------

PK_TEST_BAK                   P ENABLED

 

SQL> show recyclebin

ORIGINAL NAME   RECYCLEBIN NAME               OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ ----------------

TEST            BIN$v6zc94PYQCON0EGJ9tMvQQ==$0 TABLE       2008-11-24:20:17

SQL> flashback table test to before drop;

 

闪回完成。

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

BIN$qdWcERzdS+yMK7Vqv9E1UA==$0 TRIGGER            INVALID

PRO_TEST                      PROCEDURE          INVALID

PK_TEST_BAK                   INDEX              VALID

BIN$MEmOkWEiQ8GnifY4O+9Y8g==$0 INDEX              VALID

 

已选择6行。

 

SQL> alter index "BIN$MEmOkWEiQ8GnifY4O+9Y8g==$0" rename to IDX_TEST_OBJECT_I

 

索引已更改。

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

BIN$qdWcERzdS+yMK7Vqv9E1UA==$0 TRIGGER            INVALID

PRO_TEST                      PROCEDURE          INVALID

IDX_TEST_OBJECT_ID            INDEX              VALID

PK_TEST_BAK                   INDEX              VALID

 

已选择6行。

 

SQL> alter trigger "BIN$qdWcERzdS+yMK7Vqv9E1UA==$0" rename to trg_test;

 

触发器已更改

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

PRO_TEST                      PROCEDURE          INVALID

IDX_TEST_OBJECT_ID            INDEX              VALID

PK_TEST_BAK                   INDEX              VALID

TRG_TEST                      TRIGGER            INVALID

 

已选择6行。

 

SQL> alter trigger trg_test compile;

 

触发器已更改

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

PRO_TEST                      PROCEDURE          INVALID

IDX_TEST_OBJECT_ID            INDEX              VALID

PK_TEST_BAK                   INDEX              VALID

TRG_TEST                      TRIGGER            VALID

 

已选择6行。

 

SQL> alter procedure pro_test compile;

 

过程已更改。

 

SQL> select object_name,object_type,status from user_objects;

 

OBJECT_NAME                   OBJECT_TYPE        STATUS

------------------------------ ------------------- -------

TEST                          TABLE              VALID

TEST_BAK                      TABLE              VALID

PRO_TEST                      PROCEDURE          VALID

IDX_TEST_OBJECT_ID            INDEX              VALID

PK_TEST_BAK                   INDEX              VALID

TRG_TEST                      TRIGGER            VALID

 

已选择6行。

 

SQL> select constraint_name,constraint_type,status from user_constraints;

 

CONSTRAINT_NAME               C STATUS

------------------------------ - --------

PK_TEST_BAK                   P ENABLED

 

Flashback drop并没有把FK恢复！

 

总结：

在recyclebin on的状态下，Drop table之后，会将table和trigger重新命名，procedures则不会重命名，flashback drop可以恢复表以及相关object，但是对于procedures和trigger需要自己手动rename和compile，FK不能被恢复，需要手动创建。