oracle reinstate,Oracle10g New Feature -- 3.Flashback Table

Example:

SQL>select object_name,object_type from user_objects

OBJECT_NAMEOBJECT_TYPE

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

FLASHTABLE

IDX1_FLASHINDEX

SQL> select * from tab;

TNAMETABTYPECLUSTERID

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

FLASHTABLE

SQL> drop table flash;

Table dropped.

SQL> select * from tab;

TNAMETABTYPECLUSTERID

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

BIN$NHMSE3qoQlmhlzmeiDOQpw==$0 TABLE

SQL>select object_name,object_type from user_objects

OBJECT_NAMEOBJECT_TYPE

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

BIN$NHMSE3qoQlmhlzmeiDOQpw==$0TABLE

BIN$fJd3Ze8AQJODogj64ZE+7A==$0INDEX

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 NAMERECYCLEBIN NAMEOBJECT TYPEDROP TIME

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

FLASHBIN$NHMSE3qoQlmhlzmeiDOQpw==$0 TABLE2004-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;

TNAMETABTYPECLUSTERID

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

FLASHTABLE

SQL>select object_name,object_type from user_objects

OBJECT_NAMEOBJECT_TYPE

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

FLASHTABLE

BIN$fJd3Ze8AQJODogj64ZE+7A==$0INDEX

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_NAMEOBJECT_TYPE

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

BIN$RGaBIxRdSW65NUWeQ6NOAg==$0TABLE

BIN$uix7J5C2TomXdYKjdzGXOQ==$0INDEX

SQL> purge index idx1_flash;

Index purged.

SQL>select object_name,object_type from user_objects

OBJECT_NAMEOBJECT_TYPE

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

BIN$RGaBIxRdSW65NUWeQ6NOAg==$0TABLE

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;