Flashback Flash back table (flashback tables)

Background knowledge


Set Flashback database Parameters
Set three parameters for database flashback:
Db_recovery_file_dest_size Flash back Recovery area size
Db_recovery_file_dest Flashback Recovery Zone path, this parameter can be arbitrarily specified, the flashback log records the pre-image of the database, the log will not be archived, one but deactivate the database's flashback, the log in the directory will
Auto Clear
Db_flashback_retention_target retains the amount of data recovered for the last time, in minutes.
Sql> alter system set db_recovery_file_dest_size=5g;
System altered.
Sql> alter system set db_recovery_file_dest= '/home/oracle/flash ';
System altered.
Sql> alter system set db_flashback_retention_target=2440; --Min 2 days, system default is 1 days
System altered.




Recycle Bin (recyclebin): "Assign" a recycle Bin to each user from an administrative point of view, but the Recycle Bin does not actually open up space (logical container), and when the drop table (not purge), the data in the segment used by the original table is not actually deleted. It is actually renaming the segment name of the table in the Recycle Bin mode. The information in the Recycle Bin is automatically cleared when there is not enough table space for the segment to be expanded.


One:


Flash back a plain table without indexes and constraints


--To see if there is a flashback function on indicates that the flash back is off
Sql> Show Parameter RecycleBin


NAME TYPE VALUE
------------------------------------ ----------- ---------
RecycleBin string on


sql> desc Andy.andy;
Name Null? Type
----------------------------------------- -------- --------------
ID Number (38)


sql> drop table Andy.andy;


Table dropped.


Sql> select * from Andy.andy;
SELECT * FROM Andy.andy
*
ERROR at line 1:
Ora-00942:table or view does not exist


Sql> Flashback table Andy.andy to before drop;


Flashback complete.


Sql> select * from Andy.andy;


Id
----------
100








Two:


Flash back a table with indexes and constraints


1) Preparation Environment


Sql>
CREATE TABLE School (SID number (4), sname varchar2 (), check_status number (1) Default 0 check (Check_status in (0,1)), Accountant Varchar2 (char) unique,pwd varchar2 (char), email varchar2 (+ char), Photo_path varchar2 (+ char),
Constraint Pk_t_school primary KEY (SID));


Table created.


sql> INSERT INTO Andy.school (sid,sname) VALUES (1, ' Andy ');


1 row created.


sql> INSERT INTO Andy.school (Sid,sname) VALUES (2, ' Andy ');


1 row created.


Sql> Col sname for A40
Sql> select Sid,sname from Andy.school;


SID SNAME
---------- ----------------------------------------
1 Andy
2 Andy


--Log in with Andy User
Sqlplus Andy/[email PROTECTED]:1521/ORCL




2) Check the constraints and indexes


Sql> SELECT index_name, column_name, descend from user_ind_columns WHERE table_name = ' SCHOOL ';


Index_name column_name DESC
------------------------------ ---------------------------------------- ----
Bin$cgra2xbjp/jgugqkdrklsq==$0 SID ASC
Bin$cgra2xbkp/jgugqkdrklsq==$0 ACCOUNTANT ASC


Sql> Select Constraint_name,table_name, column_name from User_cons_columns where table_name= ' SCHOOL ';


CONSTRAINT_NAME TABLE_NAME column_name
------------------------------ ------------------------------ ----------------------------------------
Bin$cgra2xbgp/jgugqkdrklsq==$0 SCHOOL Check_status
Bin$cgra2xbhp/jgugqkdrklsq==$0 SCHOOL SID
Bin$cgra2xbip/jgugqkdrklsq==$0 SCHOOL ACCOUNTANT


3) The table is drop to the Recycle Bin, check the constraints and index


sql> drop table School;


Table dropped.


Sql> Select Index_name,table_name from user_indexes where table_name = ' SCHOOL ';


No rows selected


Sql> Select Constraint_name,table_name from user_constraints where table_name= ' SCHOOL ';


No rows selected


4) Flash back table, check the constraints and index


Sql> Flashback Table School to before drop;


Flashback complete.


Sql> SELECT index_name, column_name, descend from user_ind_columns WHERE table_name = ' SCHOOL ';


Index_name column_name DESC
------------------------------ ---------------------------------------- ----
Bin$cgra2xbjp/jgugqkdrklsq==$0 SID ASC
Bin$cgra2xbkp/jgugqkdrklsq==$0 ACCOUNTANT ASC


Sql> Select Constraint_name,table_name, column_name from User_cons_columns where table_name= ' SCHOOL ';


CONSTRAINT_NAME TABLE_NAME column_name
------------------------------ ------------------------------ ----------------------------------------
Bin$cgra2xbgp/jgugqkdrklsq==$0 SCHOOL Check_status
Bin$cgra2xbhp/jgugqkdrklsq==$0 SCHOOL SID
Bin$cgra2xbip/jgugqkdrklsq==$0 SCHOOL ACCOUNTANT


5) Renaming indexes and constraints, respectively


sql> ALTER INDEX "bin$cgra2xbjp/jgugqkdrklsq==$0" Rename to Pk_t_school;


Index altered.


sql> ALTER INDEX "bin$cgra2xbkp/jgugqkdrklsq==$0" Rename to sys_c0024861;


Index altered.


Sql> ALTER TABLE school rename constraint "bin$cgra2xbgp/jgugqkdrklsq==$0" to sys_c0024859;


Table altered.


Sql> ALTER TABLE school rename constraint "bin$cgra2xbhp/jgugqkdrklsq==$0" to Pk_t_school;


Table altered.


Sql> ALTER TABLE school rename constraint "bin$cgra2xbip/jgugqkdrklsq==$0" to sys_c0024861;


Table altered.


6) View the modified index name and constraint name
Sql> SELECT index_name, column_name, descend from user_ind_columns WHERE table_name = ' SCHOOL ';


Index_name column_name DESC
------------------------------ ---------------------------------------- ----
Pk_t_school SID ASC
sys_c0024861 ACCOUNTANT ASC


Sql> Select Constraint_name,table_name, column_name from User_cons_columns where table_name= ' SCHOOL ';


CONSTRAINT_NAME TABLE_NAME column_name
------------------------------ ------------------------------ ----------------------------------------
sys_c0024859 SCHOOL Check_status
Pk_t_school SCHOOL SID
sys_c0024861 SCHOOL ACCOUNTANT


OK, reprint please indicate the source.

Flashback Flash back table (flashback tables)