2.Oracle Flash back feature (FLASHBACK DROP & RecycleBin)

Reprinted from: 6105327

The FLASHBACK drop feature allows you to restore a specified table to its deleted point in time without losing any databases and keep the database in its current state. Flashback deletion is not
Instead of actually deleting the table, rename the table and put it in the Recycle Bin, similar to the Windows Recycle Bin. When an active object needs to use the space occupied by the table, the table is
is being deleted. The table can be restored as long as the space is not reused. This paper mainly describes the flashback drop characteristics and the management of the Recycle Bin (recyclebin) in the Flashback feature.

First, the function of FLASHBACK DROP

Restores a previously deleted table to a state before it was deleted restores the table's indexes and triggers, authorizing the restoration of the table's constraints, including unique constraints, primary key constraints, and non-null constraints. FOREIGN KEY constraint unrecoverable can implement system-based and session-based flash drop operations    alter system set RecycleBin = On | off;    Alter session Set RecycleBin = On | The Off;drop table (Oracle 10g) command does not actually delete the table and is internally mapped to the Rename command, that is, it is renamed and placed in the Recycle Bin.

Ii. the process of understanding the names of the tables

[email protected]> CREATE TABLE Tb_emp as SELECT * from EMP;  --based on the EMP table to create the table tb_emp[email protected]> ALTER TABLE TB_EMP add constraint EMPNO_PK primary key (EMPNO);    --Adding a PRIMARY KEY constraint will result in a primary key index [email protected]> ALTER TABLE TB_EMP add constraint ename_uk unique (ename);      --Add a unique constraint that will produce a unique index [email protected]> ALTER TABLE TB_EMP add constraint sal_ck check (sal>0);  --Add CHECK constraint [email protected]> ALTER TABLE tb_emp modify job constraint job_nn NOT null; --Add non-null constraint [email protected]> ALTER TABLE TB_EMP add constraint DEPT_FK foreign key (DEPTNO) References Dept (DEPTNO)   ON DELETE cascade; --Add FOREIGN KEY constraint [email protected]> select Constraint_name,constraint_type from user_constraints where Table_name= ' TB                    _emp '; --View all constraints on the Tb_emp table constraint_name C----------------empno_pk pename_uk usal_ck cjob_nn CDE PT_FK R--see below the ID of the file where the table tb_emp resides, the start ID of the block, the size, and the object ID of the object [email protected]> Select File_id,block_id,bytes from dba_extents where segment_name= ' tb_emp '; file_id block_id BYTES------------------------------4 393 65536 [email protecte D]> Select object_name,object_id from dba_objects where object_name = ' tb_emp '; --View the object ID of table tb_emp object_name object_id------------------------------tb_emp 54493--on the table in Row rename [email protected]> alter TABLE tb_emp Rename to Tb_employees; [email protected]> Select File_id,block_id,bytes from dba_extents where segment_name= ' tb_employees ';      --The ID of the file after renaming, the starting ID of the block, the size has not changed file_id block_id BYTES------------------------------4 393 65536 [email protected]> Select object_name,object_id from dba_objects where object_name = ' tb_employees '; --The object ID has not changed after renaming object_name object_id------------------------------tb_employees 54493[email&nbsp              ;p rotected]> Select Index_name,index_type--The indexes and constraints have not changed after renaming 2 from user_indexes where Table_name= ' tb_employees ' 3 UNION ALL 4 Select Constraint_name,constra Int_type 5 from User_constraints where table_name= ' tb_employees '; index_name index_type-------------- -------------------------------------------empno_pk Normalename_uk NORMALEMPN                         O_PK Pename_uk Usal_ck Cjob_nn CDEPT_FK R from the above demonstration, it can be seen that renaming a table simply modifies the table name, and the ID of the Table object, as well as where the table is stored, has not changed substantially from the beginning of the block to its size.

Iii. delete the table and implement flashback

--1. Delete the table tb_employees and view the Recycle Bin Information [email protected]> drop table tb_employees; [email protected]> Select Object_name,original_name,can_undrop,base_object from user_recyclebin;object_ NAME original_name CAN base_object------------------------------------------------- ---------------------------bin$k1zc3yeiwzvgqab/aqbrvw==$0 tb_employees YES 54493bin$k1zc3yehwzvgqab/ Aqbrvw==$0 Ename_uk No 54493bin$k1zc3yegwzvgqab/aqbrvw==$0 empno_pk No 5  4493[email protected]> Select COUNT (1) from "bin$k1zc3yeiwzvgqab/aqbrvw==$0"; --You can use the Recycle Bin name to access the object, but double quotation marks to the object COUNT (1)----------13--2. Implement flashback and view the situation after Flashback [email protected]> Flashback table Tb_empl   Oyees to before drop; --Flash back to Flashback complete.             [email protected]> Select COUNT (1) from Tb_employees;           --flash back table exists and can access COUNT (1)----------13[email protected]> Select Index_name,index_type        --look at the post-flashback index, the constraint condition, and find its name still bin$ name 2 from user_indexes where Table_name= ' tb_employees ' 3 UNION ALL 4 Select con Straint_name,constraint_type 5 from User_constraints where table_name= ' tb_employees '; index_name INDE X_type---------------------------------------------------------bin$k1zc3yegwzvgqab/aqbrvw==$0 normalbin$ K1zc3yehwzvgqab/aqbrvw==$0 normalbin$k1zc3yecwzvgqab/aqbrvw==$0 pbin$k1zc3yedwzvgqab/aqbrvw==$0 UBIN$ K1zc3yeewzvgqab/aqbrvw==$0 cbin$k1zc3yefwzvgqab/aqbrvw==$0 C from the above query can see the name of the index constraint after the flash back or use the beginning of the bin, the system-generated name, you can change it back, But the foreign KEY constraint no longer exists. --3. Attempt to table DML operation [email protected]> INSERT INTO Tb_employees (EMPNO,ENAME,JOB,SAL,DEPTNO) Select 9999, ' Robinson ' , ' DBA ', 3000,50 from dual;      1 row created. --can be successfully inserted, the foreign key constraint of the Deptno column has been deleted, so DEPTNO successfully inserted [email protected]> alter INDEX "BIN$K1ZC3YEGWZVGQAB/AQBRVW==$0" Rename to EMPNO_PK;      Index altered. --Change the index at the beginning of the bin back to its original name, and the rest of the constraint names are modified here to omit--4. The following shows a problem that cannot be flashed back to table when tablespace is low [email protected]> select Tablespace_name,sUm (bytes/1024/1024) | | '     M ' 2 from Dba_free_space where Tablespace_name= ' TBS1 ' 3 Group by Tablespace_name; --Table Space TBS1 free space is Mtablespace_name SUM (bytes/1024/1024) | | ' M '---------------------------------------------------------------TBS1.9375 m [Email prot Ected]> CREATE TABLE tb1 tablespace TBS1 as SELECT * from Dba_objects where rownum < 6000; [email protected]> Select Tablespace_name,sum (bytes/1024/1024) | | '  M ' 2 from Dba_free_space where Tablespace_name= ' TBS1 ' 3 Group by Tablespace_name; --After the table space is created for table TB1, the free space is. 25mtablespace_name SUM (bytes/1024/1024) | | M '---------------------------------------------------------------TBS1. m[email protecte   d]> drop table tb1;  --Delete the table tb1 [email protected]> show RecycleBin; --The deleted object is located in the Recycle Bin original name RecycleBin name object TYPE drop Time------------------------------------- --------- ------------ -------------------TB1 bin$k2ozseiag7lgqab/aqazma==$0 TABLE 2010-10-25:12:05:18 [email protected]> sel ECT Object_name,original_name,can_undrop, base_object from User_recyclebin;object_name Original_n can B Ase_object------------------------------------------------------bin$k2ozseiag7lgqab/aqazma==$0 TB1 YES 545 31[email protected]> Select Tablespace_name,sum (bytes/1024/1024) | | '   M ' 2 from Dba_free_space where Tablespace_name= ' TBS1 ' 3 Group by Tablespace_name;                --Tablespace TBS1 shows that the available space has been returned to 1M--but not really m, and when a tablespace is required, the oldest object of the Recycle Bin is automatically cleared to meet the current space requirements Tablespace_name SUM (bytes/1024/1024) | | M '-------------------------------------------------------------TBS1.9375 m[email protecte D]> Select Tablespace_name,autoextensible-This query shows that the tablespace TBS1 cannot automatically expand 2 from dba_data_files where tablespace_name = ' TBS 1 '; Tablespace_name AUT------------------TBS1 no[email p rotected]> CREATE table TB2 tablespace TBS1 as SELECT * FROM Dba_objects--Creates a new table in the tablespace again TB2 2 where rownum < 6000;  [email protected]> show RecycleBin; --This time the original table TB1 record in the Recycle Bin is automatically cleared [email protected]> select Object_name,original_name,can_undrop, 2 base_object from  User_recyclebin;no Rows selected[email protected]> Flashback table tb1 to before drop; --At this time the table TB1 cannot be flashed back flashback table Tb1 to before Drop*error @ line 1:ora-38305:object not in RECYCLE BIN

Iv. Management of recycling stations

Each user has their own recycle Bin, and can view the tables deleted in their own mode using the purge command to permanently delete objects, reclaim space purge commands in several common Forms    drop table tbname purge    -delete tables directly instead of being placed in the Recycle Bin    Purge Table Tbname         --clearing tbname tables in the Recycle Bin    purge index idx_name       --Clears the index in the Recycle Bin Idx_name    purge tablespace Tablespace_name   --Clears all deleted objects in the tablespace    purge tablespace tablespace_name user user_name  --clears the objects that are deleted by the specified user in the table space    purge User_recyclebin      --Clears all objects that have been deleted by the specified user    purge Dba_recyclebin       --Clears all deleted objects

V. Summary

The deletion of the above table and the distribution of space are summarized as follows: 1. The deletion of the table is mapped to renaming the table and then placing it in the Recycle Bin 2. The index of the table, the trigger, will not be affected after the authorization flashback. Index, The trigger name can be changed back to the original name as needed 3. For constraints, if a FOREIGN key constraint, the table is not recoverable after the deletion, the remaining constraints are not affected by 4. If you want to query objects in the Recycle Bin, we recommend that you enclose the object name in double quotation marks 5. The essence of flashback does not undo the transaction that has been committed 6. For deleted tables if the new object in the tablespace is being reused due to insufficient space pressure, the flashback will fail 7. For low tablespace, the system automatically clears the oldest object in the Recycle Bin to meet the current requirement of FIFO principle 8. Common methods    for flash-back tables Flashback table Tbname to before drop;    Flashback table Tbname to before drop rename to Newtbname;    The second statement is used to remove the table name has been reused, so the flashback must be renamed to a new table name, the schema does not change 9. If there are two identical original table names in the Recycle Bin, the flashback will always flash back to the most recent version, and if you flash back to a particular table, you need to specify the    name of the table in the Recycle For example    , Flashback table "Bin$k1zc3yeiwzvgqab/aqbrvw==$0" to before drop;10.flashback drop cannot flash back to the table truncated by the truncate command, but can only restore the drop The following table 11.flashback drops cannot be flashed back to the drop user Scott Cascade Delete scenario operation, this can only be used with flashback database12. Tables stored in the system Tablespace cannot enable flashback drop, And these tables are immediately deleted with two important views related to the Recycle Bin: dba_recyclebinuser_recyclebin  

2.Oracle Flash back feature (FLASHBACK DROP & RecycleBin)