flashbck to before drop

创建TEST表：
Create table test as select * from user_objects;
创建TEST_BAK表：
Create table test_bak as select * from test;
创建procedure：
Create or replace procedure pro_test
is
id_cnt number;
begin
select count(object_id) into id_cnt from test;
dbms_output.put_line('Object_id:'||id_cnt);
end;
 创建trigger：
 
CREATE OR REPLACE TRIGGER "TEST"."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和index重新命名，table,trigger状态无效，index状态有效，procedures则不会重命名，但是状态无效，flashback drop可以恢复表以及相关object，但是对于procedures，trigger和index需要自己手动rename和compile，FK不能被恢复，需要手动创建。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22253646/viewspace-667351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22253646/viewspace-667351/