首页 » ORACLE » flashback drop table,index is recovery?

flashback drop table,index is recovery?

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.

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Mande | #1
    2011-11-12 at 01:15

    Okay I’m convinced. Let’s put it to aicton.