Oracle Flashback: Flashback table and flashback drop

In Oracle 10 Gb, the Flash back family is divided into the following members:
Flashback Database
Flashback Drop
Flashback Table
Flashback Query (Flashback Query, Flashback Version Query, and Flashback Transaction Query)
The following describes Flashback Drop and Flashback Table.
 
Flashback DROP
 
Flashback Drop occurs from Oracle 10 GB and is used to restore objects accidentally deleted by users (including tables and indexes). This technology relies on Tablespace Recycle Bin (Tablespace Recycle Bin ), this function is very similar to the recycle bin of windows.
This function has no relationship with the flashback parameter and does not require row movement for tables.
Tested SQL> alter database flashback off can flash back to drop the table.
Flashback does not support objects in the sys user. system tablespace and cannot be obtained from the recycle bin. Therefore, show recyclebin is empty when SYS or SYSTEM users log on.
1. Tablespace Recycle Bin
Starting from Oracle 10 Gb, each tablespace has a logical region called the recycle bin. When you run the drop command, the deleted table and associated objects (including indexes and constraints) of the table are deleted, trigger, LOB segment, and LOB index segment) will not be physically deleted. These objects are first transferred to the recycle bin, which provides users with a possibility of recovery.
The recyclebin initialization parameter is used to control whether to enable the recyclebin function. The default value is ON and can be disabled with OFF.
SQL> show parameter recycle
Recyclebin string on
 
Disable this function:
SQL> alter system set recyclebin = off;
SQL> alter system set recyclebin = on;
SQL> alter session set recyclebin = off;
SQL> alter session set recyclebin = on;
After the deletion is disabled, the deleted object will be deleted directly and will not be written to Recycle. Of course, when you delete the object, specify the purge parameter, and the table will be deleted directly without being written to recyclebin.
 
The Recycle Bin area of a tablespace is only a logical region, instead of physically setting a partition from the tablespace for the Recycle Bin. Therefore, the Recycle Bin area shares the storage region of the tablespace with common objects, or the Recycle Bin object needs to snatch the storage space from the common object. When space is insufficient, Oracle overwrites the objects in Recycle Bin in the first-in-first-out order. You can also manually delete the space occupied by the Recycle Bin.
 
1). Purge tablespace tablespace_name: used to clear the Recycle Bin of the tablespace
2). Purge tablespace tablespace_name user user_name: clears the objects of the specified user in the Recycle Bin of the specified tablespace.
3). Purge recyclebin: deletes the objects in the Recycle Bin of the current user.
4). Purge dba_recyclebin: deletes the objects in Recycle Bin of all users. This Command requires sysdba permission.
5). Drop table table_name purge: delete objects permanently and cannot be restored using Flashback instead of in Recycle Bin.
6). Purge index recycle_bin_object_name: When you want to release the space of the Recycle bin and restore the table, you can release the space occupied by the index of the object to relieve the space pressure. Because indexes can be rebuilt.
 
2. Flashback Drop instance operation
 
The following operations are performed when the system parameter recyclebin = on:
1. SQL> drop table test1;
Table dropped.
 
2. View recyclebin Information
SQL> col origninal_name format a15;
SQL> col type format a15;
SQL> select object_name, original_name, type from user_recyclebin;
 
OBJECT_NAME ORIGINAL_NAME TYPE
---------------------------------------------------------------------
BIN $ fr5 g/49 + SZ2oESrTX4UCHg = $0 IDX_TESTID INDEX
BIN $ x1Ey4hTFSeilywuQ7KKM + w ==$ 0 TEST1 TABLE
SQL> show recyclebin; -- note that this is the sqlplus command
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
TEST1 BIN $ x1Ey4hTFSeilywuQ7KKM + w ==$ 0 TABLE 2012-01-13: 16: 35: 24
 
3. Flash the deleted table back
SQL> flashback table test1 to before drop;
Flashback complete.
SQL> select * from test1;
ID NAME
--------------------
3763392
 
In this case, after table test1 is deleted, an object test1 (table or procedure...) with the same name is created. You need to rename it when flashing back.
Flashback table test1 to before drop rename to testX
Another case is that tables with the same name are repeatedly dropped to the recyclebin, and the following principle is followed.
Once the flash recovery is completed, the objects in the Recycle Bin disappear.
SQL> select object_name, original_name, type from user_recyclebin;
No rows selected
 
Flashbacktable drop is not supported after recyclebin is set to off.
SQL> alter system set recyclebin = off;
System altered.
SQL> drop table test1;
Table dropped.
SQL> select object_name, original_name, type from user_recyclebin;
No rows selected
 
Notes for Flashback Drop:
1). It can only be used for non-system tablespace and locally managed tablespace.
2). The reference constraints of the object will not be restored. The foreign key constraints pointing to the object need to be rebuilt.
3) whether the object can be restored successfully depends on whether the object space is overwritten and reused.
4 ). when you delete a table, the materialized view that depends on the table is also deleted, but the materialized view is not placed in the recycle bin. Therefore, when you execute flashback table to before drop, you cannot restore the materialized view that depends on it. You need to manually create a new view by the dba.
5). For objects in Recycle Bin, only query is supported.
 
Flashback Table
 
Note that the SYS user does not support flashback, as described above.
Flashback Table also uses the UNDO tablespace content to implement data rollback. This command is relatively simple. Enter flashback table table_name to scn (to timestamp.
Note: To flashback a table, you must allow row movement of the table.
Alter table table_name row movement;
To check whether row movement is enabled for a table, you can query it in user_tables (or all_tables, dba_tables ),
For example:
SQL> select row_movement from user_tables where table_name = 'test1 ';
ROW_MOVE
--------
DISABLED
Row movement is not supported at this time.
 
SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
3769840
SQL> delete from test1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> flashback table test1 to scn 3769840;
Flashback table test1 to scn 3769840
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
In this case, the above error occurs because row movement is not supported.
SQL> alter table test1 enable row movement;
Table altered.
SQL> flashback table test1 to scn 3769840;
Flashback complete.
SQL> select * from test1;
ID
----------
1
The Flashback table command allows you to operate on multiple tables at the same time. table names can be separated by commas. If you specify multiple tables when executing a flashback table command, remember that a single flashback table is in the same transaction, so the restoration operations of these tables are either successful or failed.
For example, flashback table a, B, c to scn 1103864;
 
Undo-based table recovery, pay attention to the impact of DDL operations
For example, after truncate table cannot flashback table, an error occurs: ORA-01466: unable to read data-table definition has changed.
Other operations include: drop/modify column, move table, drop partition (if any), truncate table/partition. These operations will invalidate the undo tablespace data revocation, applying flashback query to tables that have performed these operations triggers a ORA-01466 error. In addition, although some table structure modification statements do not affect the undo tablespace revocation record, it is possible that the record duplication in the undo table cannot be applied due to table structure modification. For example, if a constraint is added, however, the undo record queried by flashback does not meet the new constraints, and direct recovery is obviously not successful at this time. You must either temporarily disable the record or use appropriate logic, after the data to be restored is processed, the data is restored.
In addition, flashback query is invalid for dynamic performance views such as v $ tables and x $ tables, but it is effective for data dictionaries such as dba _ *, all _ *, and user. At the same time, this feature fully supports accessing remote databases, such as select * from tbl @ dblink as of scn 360.
2. for undo-based table restoration, flashback table actually performs dml operations (dml locks will be applied to the operated table). Therefore, you need to pay attention to the impact of triggers on it. By default, flashback table to scn/timestamp will automatically disable triggers that are different from the operating table during execution. If you want to continue using trigger during this period, you can append the triggers after the flashback table
Enable triggers clause.


Author kkdelta