ANBOB™

Oracle Flashback Drop lets you reverse the effects of dropping a table, returning the dropped table to the database along with its dependent objects such as indexes and triggers. It works by storing dropped objects in a Recycle Bin, from which they may be retrieved until the Recycle Bin is purged, either explicitly or because space is needed for new database objects.

sys@ORCL> conn anbob/anbob

Connected.

anbob@ORCL> create table testfbk(id number(10),name varchar2(10));

Table created.
anbob@ORCL> create index idx_testfbk_id on testfbk(id);
Index created.
anbob@ORCL> select table_name,tablespace_name from user_tables where table_name='TESTFBK';
TABLE_NAME                     TABLESPACE_NAME

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

TESTFBK                        USERS
anbob@ORCL> select index_name,tablespace_name from user_indexes where index_name='IDX_TESTFBK_ID';
INDEX_NAME                     TABLESPACE_NAME

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

IDX_TESTFBK_ID                 USERS
anbob@ORCL> drop table testfbk;
Table dropped.
anbob@ORCL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TESTFBK          BIN$p2UI3ewN2H7gQAB/AQANTw==$0 TABLE        2011-07-06:18:26:34

anbob@ORCL> select index_name,tablespace_name from user_indexes where index_name='IDX_TESTFBK_ID';
no rows selected
anbob@ORCL> flashback table testfbk to before drop;
Flashback complete.
anbob@ORCL> select table_name,tablespace_name from user_tables where table_name='TESTFBK';
TABLE_NAME                     TABLESPACE_NAME

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

TESTFBK                        USERS
anbob@ORCL> select index_name,tablespace_name,status from user_indexes where index_name='IDX_TESTFBK_ID';
no rows selected
anbob@ORCL> create table testfbk1(id number(10),name varchar2(10)) tablespace system;
Table created.
anbob@ORCL> select table_name,tablespace_name from user_tables where table_name='TESTFBK1';
TABLE_NAME                     TABLESPACE_NAME

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

TESTFBK1                       SYSTEM
anbob@ORCL> drop table testfbk;
Table dropped.
anbob@ORCL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TESTFBK          BIN$p2UI3ewP2H7gQAB/AQANTw==$0 TABLE        2011-07-06:18:31:09
anbob@ORCL> create table testfbk2(id number(10) primary key,name varchar2(10)) tablespace tt;
Table created.
anbob@ORCL> desc testfbk2;

 Name                                         Null?    Type

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

 ID                                          NOT NULL NUMBER(10)

 NAME                                                 VARCHAR2(10)
anbob@ORCL> drop table testfbk2;
Table dropped.
anbob@ORCL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TESTFBK          BIN$p2UI3ewP2H7gQAB/AQANTw==$0 TABLE        2011-07-06:18:31:09

TESTFBK2         BIN$p2UI3ewS2H7gQAB/AQANTw==$0 TABLE        2011-07-06:18:33:50
anbob@ORCL> flashback table testfbk2 to before drop;
Flashback complete.
anbob@ORCL> desc testfbk2;

 Name                                   Null?    Type

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

 ID                                     NOT NULL NUMBER(10)

 NAME                                            VARCHAR2(10)
anbob@ORCL> select index_name,tablespace_name,status from user_indexes where table_name='TESTFBK2';
INDEX_NAME                     TABLESPACE_NAME                STATUS

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

BIN$p2UI3ewR2H7gQAB/AQANTw==$0 TT                             VALID
anbob@ORCL> insert into testfbk2 values(1,'aa');
1 row created.

anbob@ORCL> select * from testfbk2 where id='1';
        ID NAME

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

         1 aa
Execution Plan

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

Plan hash value: 2124593719
--------------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                                |     1 |     4 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFBK2                       |     1 |     4 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | BIN$p2UI3ewR2H7gQAB/AQANTw==$0 |     1 |       |     0   (0)| 00:00:01 |

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

For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, tables in the SYSTEM tablespaces cannot recovered using Flashback Drop regardless of the tablespace type.

打赏

微信扫一扫，打赏作者吧～