闪回删除的表

ginaduxuefang

浏览: 90475 次
性别:
来自: 北京

最近访客更多访客>>

fenix99

lolo9101001

lang3126

studyTan

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

flashback before drop

创建TEST表：

Create table test as select * from user_objects;

创建TEST_BAK表：

Create table test_bak as select * from test;

创建procedure：

createorreplaceprocedurepro_test

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不能被恢复，需要手动创建。