Flashback six major technologies: flashback drop

Environment:

sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

1 recyclebin

 

Recyclebin is introduced in Oracle 10 Gb. to delete an object, Oracle first modifies the data dictionary, renames it and its associated objects (indexes, constraints, and so on), and puts it in recyclebin. The deleted object will occupy the same space size when it is created. When there is space pressure, the space will be recycled slowly. However, after an object is deleted, this part of space is considered as free space, reusable, and traceable in dba_free_space. If you can confirm to delete the object, you can use the purge command to completely delete it, which can reduce the performance cost of dynamic space recovery.

 

Each user has its own recyclebin. There are many ways to view recyclebin. The most common method is select * From recyclebin. The simplest method is show recyclebin. Note that show recyclebin only lists the base tables. The associated objects of the deleted tables are not displayed. The object name in recyclebin can also be used as a normal name. The only difference is that it cannot be rename.

 

● The tablespace does not have enough free space and no new space can be expanded.
● The tablespace must create new objects and allocate space.
In this case, the space pressure on the recyclebin is caused, which is the only reason that the recyclebin actively deletes the object.

 

Clear objects in recyclebin
(I) you can disable or enable recyclebin by modifying initialization parameters at the session or instance level.
The default value is on.

hr@ORCL> show parameter recyclebinNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------recyclebin                           string      onhr@ORCL> create table tt as select * from jobs;Table created.hr@ORCL> show parameter recyclebinNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------recyclebin                           string      onhr@ORCL> alter session set recyclebin=off;Session altered.hr@ORCL> drop table tt;Table dropped.hr@ORCL> select * from recyclebin;no rows selected

(Ii) Drop .. purge;
(Iii) clear existing tablespace objects
There are three methods:
Purge Table Original Name or recyclebin name
Purge tablespace tbs_name
Purge recylebin

 

2. Notes

 

① Flashback drop cannot restore the integrity of the reference (that is, the relationship between the primary and Foreign keys). After the restoration, the constraint is disable and must be manually processed.
② The operated tables must be local tablespace Management
③ The name of the associated object (index, constraint, etc.) of the restored table is not automatically restored to the name before deletion, but is automatically generated by the system. You must manually change the name of the DBA. In addition, bitmap indexes and materialized views cannot be restored.
④ When the space is insufficient, the indexes of the deleted table will be cleared first.
⑤ Flashback drop supports simultaneous operations on multiple tables, separated by commas
⑥ Flashback drop can only restore tables deleted by the drop command

 

3. Experiment

(I) simplest table deletion and Restoration

hr@ORCL> drop table t2;Table dropped.hr@ORCL> select object_name,original_name from recyclebin;OBJECT_NAME                    ORIGINAL_NAME------------------------------ --------------------------------BIN$yUcFsAG7oGvgQAB/AQAjTg==$0 T2hr@ORCL> flashback table t2 to before drop;Flashback complete.hr@ORCL> select count(*) from t2;  COUNT(*)----------        19hr@ORCL> select object_name,original_name from recyclebin;no rows selected

(Ii) slightly more complex table recovery
If the table to be restored already has a table with the same name in the current schema, an error is returned when the table is restored directly.

hr@ORCL> drop table t2;Table dropped.hr@ORCL> create table t2 as select * from jobs;Table created.hr@ORCL> flashback table t2 to before drop;flashback table t2 to before drop*ERROR at line 1:ORA-38312: original name is used by an existing objecthr@ORCL> flashback table t2 to before drop rename to t2_recov;Flashback complete.hr@ORCL> select count(*) from t2_recov;  COUNT(*)----------        19

(3) restore from multiple Deletions
Add multiple names in the same table to recyclebin.

hr@ORCL> drop table t2;Table dropped.hr@ORCL> alter table t2_recov rename to t2;Table altered.hr@ORCL> drop table t2;Table dropped.hr@ORCL> select object_name,original_name,droptime from recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME------------------------------ -------------------------------- -------------------BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12BIN$yUcFsAHBoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:48:42hr@ORCL> flashback table t2 to before drop;Flashback complete.hr@ORCL> select object_name,original_name,droptime from recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME------------------------------ -------------------------------- -------------------BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12hr@ORCL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------REGIONS                        TABLECOUNTRIES                      TABLELOCATIONS                      TABLEDEPARTMENTS                    TABLEJOBS                           TABLEEMPLOYEES                      TABLEJOB_HISTORY                    TABLEEMP_DETAILS_VIEW               VIEWLOGMNR_TEST                    TABLEBIN$yUcFsAG/oGvgQAB/AQAjTg==$0 TABLET2                             TABLE11 rows selected.hr@ORCL> drop table t2;Table dropped.hr@ORCL> select object_name,original_name,droptime from recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME------------------------------ -------------------------------- -------------------BIN$yUcFsAHDoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:52:39BIN$yUcFsAG/oGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:47:12hr@ORCL> flashback table "BIN$yUcFsAG/oGvgQAB/AQAjTg==$0" to before drop;Flashback complete.hr@ORCL> select count(*) from t2;  COUNT(*)----------        19hr@ORCL> select object_name,original_name,droptime from recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME------------------------------ -------------------------------- -------------------BIN$yUcFsAHDoGvgQAB/AQAjTg==$0 T2                               2012-09-09:23:52:39