Flashback six major technologies: flashback drop

Flashback: flashback drop environment: [SQL] sys @ ORCL> select * from v $ version where rownum = 1; www.2cto.com BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod sys @ ORCL>! Uname-a Linux localhost. localdomain 2.6.18-308. el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux 1 recyclebin introduces recyclebin in oracle 10 Gb. For the deletion of an object, oracle first modifies the data dictionary, rename it and its associated objects (indexes, constraints, etc.) and put 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. Www.2cto.com 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 there is no new space available for expansion operations. The tablespace needs to create new objects. In this case, the space allocated will cause the recyclebin space pressure, this is the only cause that triggers recyclebin to actively delete objects. Clear objects in recyclebin (I) Modify initialization parameters to disable or enable recyclebin. Session-level or instance-level permission is supported. The default parameter is on [SQL] hr @ ORCL> show parameter recyclebin NAME TYPE VALUE =---------------------------------------- recyclebin string on hr @ ORCL> create table tt as select * from jobs; www.2cto.com Table created. hr @ ORCL> show parameter recyclebin name type value -------------------------------------- ---------------------------------- recyclebin string on hr @ ORC L> 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; (3) there are three ways to clear existing objects in a tablespace: purge table original name or recyclebin name purge tablespace tbs_name purge recylebin www.2cto.com 2 Note ① flashback drop cannot restore the integrity of reference (that is, the relationship between the primary and Foreign keys). After restoration, the constraint is disable, manual processing ② the table to be operated must be a local tablespace management ③ the associated object (index, constraint, etc.) of the restored table, its name will not be automatically restored to the name before deletion, and 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, use commas to separate tables. 6. flashback drop: Only tables deleted by the drop command can be restored. 3. Experiment. 1. The simplest way to delete a table is to restore a Table. [SQL] hr @ ORCL> drop table t2; Table dropped. hr @ ORCL> select object_name, original_name from recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN $ export/AQAjTg = $0 T2 hr @ ORCL> flashback table t2 to before drop; Flashback complete. hr @ ORCL> select count (*) from t2; COUNT (*) ---------- 19 hr @ ORCL> select object_name, original_name from recyclebin; no rows selected (II) a little more complex table recovery. If the table to be restored already has a table with the same name in the current schema, the system reports the error [SQL] hr @ ORCL> drop table t2; www.2cto.com 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 object hr @ ORCL> flashback table t2 to before drop rename to t2_recov; Flashback complete. hr @ ORCL> select count (*) from t2_recov; COUNT (*) ---------- 19 (3) Restore multiple identical tables from multiple deletions and add them to recyclebin, then go to [SQL] hr @ ORCL> drop table t2; www.2cto.com 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 too large BIN $ yucf.pdf/oGvgQAB/AQAjTg = $0 T2 2012-09-09: 23: 47: 12 BIN $ yUcFsAHBoGvgQAB/AQAjTg = $0 T2 2012-09-09: 23: 48: 42 www.2cto.com hr @ ORCL> flashback table t2 to before drop; Flashback complete. hr @ ORCL> select object_name, original_name, droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME too large BIN $ yucf.pdf/oGvgQAB/AQAjTg = $0 T2 2012-09-09: 23: 47: 12 hr @ ORCL> select * from tab; tname tabtype clusterid partition ------- ---------- regions table countries table locations table partition tables jobs table employees table JOB_HISTORY TABLE partition VIEW LOGMNR_TEST table bin $ yucf.pdf/oGvgQAB/AQAjTg = $0 TABLE T2 TABLE partition 11 rows selected. hr @ ORCL> drop table t2; Table dropped. hr @ ORCL> select object_name, original_name, droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME DROPTIME when starting ------------------- BIN $ export/AQAjTg = $0 T2 2012-09-09: 23: 52: 39 BIN $ yucf.pdf/oGvgQAB/AQAjTg = $0 T2 2012-09-09: 23: 47: 12 hr @ ORCL> flashback table "BIN $ yucf.pdf/oGvgQAB/AQAjTg = $0" to before drop; Flashback complete. hr @ ORCL> select count (*) from t2; www.2cto.com COUNT (*) ---------- 19 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: 39