Google+ Followers

Pages

Thursday, September 23, 2010

Flashback Drop Table

Flashback Drop Table:
When you issue the DROP TABLE command, Oracle merely renames the table and  moves it to a recycle bin.
The recycle bin is merely a data dictionary table that maintains information about dropped tables.
You can use the SELECT command to query the objects in the recycle bin. You can’t use INSERT, UPDATE, and DELETE commands on these objects.

You can view the contents of the recycle bin by using either the DBA_RECYCLEBIN or USER_RECYCLEBIN.


SQL> DROP TABLE EMP;


SQL> SELECT   OBJECT_NAME,    ORIGINAL_NAME,    OWNER,    TYPE ,    SPACE
FROM DBA_RECYCLEBIN;

OBJECT_NAME                                                             ORIGINAL_N              OWNER                      TYPE           SPACE
------------------------------                               ----------                            ------------  ------             ----------
BIN$5j9tXNl6Tn+94FZ4H/ylyQ==$0               PK_EMP                     SCOTT                       INDEX            8
BIN$hJ1qy1xyRomz6c40oBqQvg==$0            EMP                          SCOTT                       TABLE           8

When we drop any table the associated object like also dropped. For example if we drop EMP table the index associated with emp also dropped.

Table are restore with the original name that reside before drop

INDEX also flashback but name not change

Constraint name different from index name

11:56:29 SQL> DROP TABLE SCOTT.E1;
Table dropped.

11:56:34 SQL> FLASHBACK TABLE SCOTT.E1 TO BEFORE DROP ;
Flashback complete.

OR



11:56:34 SQL> FLASHBACK TABLE SCOTT.E1 TO BEFORE DROP  RENAME TO E2;
Flashback complete.

No comments:

Post a Comment