Oracle10g New Feature -- 3.Flashback Table

Oracle 10g引入了recyclebin，類似於windows的回收站，使你能夠很方便的恢復誤刪的表。

但是對於truncate table，好像還是沒有一種快速的恢復方法

[@more@]

1.    Flashback Table

Example:

SQL>select object_name,object_type from user_objects

OBJECT_NAME                 OBJECT_TYPE

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

FLASH                                   TABLE

IDX1_FLASH                        INDEX

 

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

FLASH                          TABLE

 

SQL> drop table flash;

Table dropped.

 

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

BIN$NHMSE3qoQlmhlzmeiDOQpw==$0 TABLE

 

SQL>select object_name,object_type from user_objects

OBJECT_NAME                                                        OBJECT_TYPE

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

BIN$NHMSE3qoQlmhlzmeiDOQpw==$0                 TABLE

BIN$fJd3Ze8AQJODogj64ZE+7A==$0                      INDEX

dropped table FLASH, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table.

The indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table

The table and its associated objects are placed in a logical container known as the "recycle bin,"

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                   OBJECT TYPE  DROP TIME

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

FLASH            BIN$NHMSE3qoQlmhlzmeiDOQpw==$0 TABLE             2004-09-08:16:56:11

 

ORIGINAL NAME: the original name of the table

RECYCLEBIN NAME: the new name in the recycle bin

 

reinstate the table

SQL>  flashback table flash to before drop;

Flashback complete.

 

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

FLASH                          TABLE

 

SQL>select object_name,object_type from user_objects

OBJECT_NAME                                        OBJECT_TYPE

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

FLASH                                                        TABLE

BIN$fJd3Ze8AQJODogj64ZE+7A==$0    INDEX

 

SQL>alter index “BIN$fJd3Ze8AQJODogj64ZE+7A==$0” rename to idx1_flash;

 

SQL> show recyclebin

“EMPTY”

The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.

To free the space

SQL>purge recyclebin

drop the table completely

SQL> drop table flash purge;

Table dropped.

 

SQL> show recyclebin

“EMPTY”

 

Managing the Recycle Bin

purge the specific table named TEST from the recycle bin after its drop

SQL>PURGE TABLE TEST;

 

or using its recycle bin name:

SQL>PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

 

permanently drop an index from the recycle bin

SQL> drop table flash;

Table dropped.

 

SQL>select object_name,object_type from user_objects

OBJECT_NAME                                                 OBJECT_TYPE

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

BIN$RGaBIxRdSW65NUWeQ6NOAg==$0     TABLE

BIN$uix7J5C2TomXdYKjdzGXOQ==$0          INDEX

 

SQL> purge index idx1_flash;

Index purged.

 

SQL>select object_name,object_type from user_objects

OBJECT_NAME                                                 OBJECT_TYPE

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

BIN$RGaBIxRdSW65NUWeQ6NOAg==$0     TABLE

 

purge all the objects in recycle bin in a tablespace USERS

PURGE TABLESPACE USERS;

 

purge only the recycle bin for a particular user in that tablespace

PURGE TABLESPACE USERS USER SCOTT;

A user such as SCOTT would clear his own recycle bin with

PURGE RECYCLEBIN;

You as a DBA can purge all the objects in any tablespace using

PURGE DBA_RECYCLEBIN;

 

Table Versions and Flashback

 

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (1);

COMMIT;

DROP TABLE TEST;

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (2);

COMMIT;

DROP TABLE TEST;

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (3);

COMMIT;

DROP TABLE TEST;

At this point, if you were to flash-back the table TEST, the third version of the table is retrieved, not the first. So the column COL1 will have the value 3, not 1.

you can also retrieve the other versions of the dropped table

• Use the rename option:

SQL>FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;

SQL>FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

• Use the specific recycle-bin names of the table to restore. To do that, first identify the table's recycle bin names and then issue:

SQL>FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;

SQL>FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;