Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 3 June 2012

Flashback Drop Table in Oracle 10g

Flashback Table feature in Oracle Database 10g helps to reverses the effects of a DROP TABLE operation. Flashback Drop is faster or easier than any other recovery mechanism that can be used in that situation.
For example:
This statement places the EMPLOYEE_DEMO table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:
SQL> DROP TABLE EMPLOYEE_DEMO;
Table Dropped
Status after Drop:
SQL> Select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
The dropped table EMPLOYEE_DEMO, 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. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.
View the object in Recycle Bin:
SQL> show recyclebin
ORIGINAL    NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
——————————  —————————— ———— ——————
EMPLOYEE_DEMO   BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2012-05-01:20:17:22
SQL> SELECT object_name as recycle_name, original_name, type FROM recyclebin;
RECYCLE_NAME                      ORIGINAL_NAME          TYPE
--------------------------------  ---------------------  ----------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  EMPLOYEE_DEMO          TABLE
BIN$JKS983293M1dsab4gsz/I249==$0  I_EMP_DEMO             INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0  LOB_EMP_DEMO           LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0  LOB_I_EMP_DEMO         LOB INDEX
This shows the original name of the table, EMPLOYEE_DEMO, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop.
Note: the exact name may differ by platform.
Reverse the Drop command:
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
SQL> FLASHBACK TABLE EMPLOYEE_DEMO TO BEFORE DROP;
FLASHBACK COMPLETE.
Assigning new name to the Flashbacked Table:
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP RENAME TO int2_admin_emp;
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
EMPLOYEE_DEMO TABLE
The table is reversed to its proper place. If you check the recycle bin now, it will be empty. Dropping a table will not directly free up space in the original tablespace. To free the space you need to purge it.
Completely drop or purging:
SQL>PURGE RECYCLEBIN;
SQL>DROP TABLE EMPLOYEE_DEMO PURGE;
SQL>PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
If you are sure that you will not want to recover a table later, you can drop it immediately and permanently
Enabling and Disabling the Recycle Bin:
ALTER SESSION SET RECYCLEBIN=OFF;
ALTER SESSION SET RECYCLEBIN=OFF;
Either you can use it directly into the Pfile RECYCLEBIN=OFF
Restriction on Flashback Drop:
·   There is no fixed amount of space pre-allocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the
·   Recycle bin. Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace.
·   You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
·   The recycle bin functionality is only available for non-system, locally managed tablespaces.
·   It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
·   The recycle bin does not preserve referential constraints on a table. If a table had referential constraints before it was dropped then recreate any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
·   Tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
·   Partitioned index-organized tables are not protected by the recycle.

0 comments:

Post a Comment