oracle 10G flashback learning

Project development, the front desk staff does not accidentally delete the configuration table data, I use this method to restore the deleted data, follow these steps 
– First start mobile 
alter table table_name enable row movement; 
– Point in time recovery (in this case based only on point-in-time recovery, and another scn recovery after introduction) 
flashback table table_name to timestamp to_timestamp (‘2010-03-27 9:00:00 ‘,’ yyyy-mm-dd hh24: mi: ss’); 
– View recovery method 
select * from table_name

– Here are some relevant information    

flashback: flashback is from Oracle9i began an operation to restore function, enhanced and modified in Oracle10g,through flashback, the user can accomplish many impossible to recover, oracle10g flashback include the following characteristics;

Oracle falshback Database feature allows Oracle by FLASHBACK DATABASE statement, the database will be rolled before a point in time or SCN, without the need for point in time recovery! 
The> oracle falshback table characteristics allow Oracle through flashback table statement, the table will be rolled before a point in time or SCN. 
Oracle falshback drop feature allows oracle recovery drop off the table or index. 
4> oracle falshback version query. Characteristics can be specific table in a certain period of time any modification records! 
5> Oracle falshback to transaction The query characteristics can restrict the user to check the database in a transaction level modify operation applies to diagnose the problem, analyze the performance, the Audit Services.

A: oracle falshback table

the Oracle Flashback table that flashback table 
Operation: Operation 1> flashback table tablename to befor drop; 
Mistakenly drop table can use this to restore the drop table! 
This operation requires the user has the following permissions: 
A: falshback any table system privilege or a form of a flashback permission. 
B: table of select, insert, DELECT alter permissions. 
C: table row Movement permissions 
SQL> set serveroutput on 
SQL> set time on 
11:24:37 SQL> set feadback off; 
11:24:59 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
TABLE A1 
A TABLE 
———————- 
The user can see Table A, now drop it; 
———————– 
11:25:01 SQL> drop table a; 
11:26:18 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
BIN $ dFFlS6jJQLShRwRQ + kLJMg == $ 0 TABLE 
TABLE A1 
———————- 
The table has been deleted, you can see that the table is not deleted in the true sense, but rather more a “the BIN $ dFFlS6jJQLShRwRQ + kLJMg == $ 0” in the table, this table is the original Table A, which is the oracle recyclebin A similar mechanism of waste recycling. When the user deletes a table after the table into the recyclebin, rather than removed! 
———————- 
Table A command to restore the 
11:26:29 SQL> flashback table a to before drop; 
11:30:45 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
TABLE A1 
A TABLE 
See Table A has been restored!

Operation: Operation 2> flashback table tablename to scn / timestamp (a timestamp); 
This operation can be use the scn (described later) or the the timestamp table will be restored to a certain point in time! 
—– Table row Movement permissions 
11:41:49 SQL> alter table a enable row movement; 
—- Insert a record 
11:44:16 SQL> insert into a values ??(‘www’, 11); 
11:45:15 SQL> commit; 
To —- the record SCN: 1,576,479 
11:45:20 SQL> select a. *, Ora_rowscn from a;

NAME AGE ORA_ROWSCN 
—————————— 
500651576479 
600651576479 
111101576479 
2221001576479 
333301576479 
444401576479 
555501576479 
www 11 1576479 
888341573035 
 Delete a record 
11:45:55 SQL> delete from a where name = ‘www’; 
11:46:19 SQL> commit; 
— SCN command to restore the deleted record! 
11:46:20 SQL> flashback table a to scn 1576479; 
11:46:49 SQL> select * from a;

NAME AGE 
——————– 
50065 
600 65 
11110 
222 100 
33330 
44440 
55550 
www 11 
88834 
— Timestamp command to restore the deleted record! 
11:46:54 SQL> flashback table a to timestamp to_timestamp (‘2007-04-27 11:45:20 ‘,’ yyyy-mm-dd hh24: mi: ss’);
11:56:28 SQL> select * from a;

NAME AGE 
——————– 
50065 
600 65 
11110 
222 100 
33330 
44440 
55550 
88834 
Attachment: – scn introduction: 
The SCN: system change numbers, oracle10g each table has ora_rowscn column! Desc can not see the change column. 
Can through select ORA_ROWSCN the FROM table to get a result set of the SCN, TIMESTAMP_TO_SCN () scnconverted to stmestamp; SCN_TO_TIMESTAMP () to convert the timestamp to scn. 
Second: flashback drop 
The use of flashback drop the oracle10g can DDL operations to recover oracla recyclebin to collect the object is deleted, similar to the Recycle Bin In fact, the object is deleted when the oracle object written to a data dictionary table, when users do not need the object , you can use the purge command to be cleared from the Recycle Bin. 
 Look at user table 
SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
SYS_TEMP_FBT TABLE. 
TABLE A1 
A TABLE

Executed in 0.078 seconds 
– Drop table A1 
SQL> drop table a1;

Executed in 0.047 seconds 
 And then look at all the tables owned by the user; 
SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
SYS_TEMP_FBT TABLE. 
A TABLE 
BIN $ fPHKLxsBTBq6 + bjBLPEJaw == $ 0 TABLE

Executed in 0.078 seconds

 As you can see the the A1 table drop at the same time, under the current user has generated a BIN at thebeginning of the new table BIN $ fPHKLxsBTBq6 that + bjBLPEJaw == $ 0, this is the original A1 form 
SQL> set timing off 
SQL> set time on 
14:25:40 SQL> select t.object_name, t.original_name from recyclebin t;

OBJECT_NAME ORIGINAL_NAME 
————————————————– ———— 
BIN $ fPHKLxsBTBq6 + bjBLPEJaw == $ 0 A1 
— Flashback table command to restore just delete Table A1 
14:29:45 SQL> flashback table a1 to before drop; 
14:29:49 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
SYS_TEMP_FBT TABLE. 
TABLE A1 
A TABLE 
 When the user does not need the object, can be used pruge to completely delete a change object 
14:29:55 SQL> drop table a1; 
14:31:13 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
SYS_TEMP_FBT TABLE. 
A TABLE 
BIN $ N + i42FTvSSemvMrH6frCQg == $ 0 TABLE

14:31:32 SQL> purge table “BIN $ N + i42FTvSSemvMrH6frCQg == $ 0”; 
14:31:49 SQL> select * from tab;

TNAME TABTYPE CLUSTERID 
———————————————– 
TABLE B 
STEST TABLE 
TA TABLE 
GROUPINGTEST TABLE. 
COLTABLE TABLE. 
ROWTABLE TABLE. 
COLTABLE1 TABLE 
ROWTABLE1 TABLE 
SYS_TEMP_FBT TABLE. 
A TABLE 
– Purge command to close stations attempt to empty the Recycle Bin in the other table 
14:31:56 SQL> purge recyclebin; 
14:34:02 SQL> select object_name from recyclebin;

OBJECT_NAME 
—————————— 
Oracle falshback version query – Flashback Versions Query 
Oracle10g falshback can all committed rows records, similar to the audit function can query falshback when to perform the operation, very convenient, including Flashback Versions Query and audit. 
1> Flashback Versions query 
14:41:22 SQL> insert into a values ??(‘zhao’, 24); 
14:42:01 SQL> commit; 
 Query the current version 
14:34:45 SQL> select a. *, Ora_rowscn from a;

NAME AGE ORA_ROWSCN 
—————————— 
500651577059 
600651577059 
111101577059 
2221001577059 
333301577059 
444401577059 
555501577059 
888341573035

14:41:22 SQL> insert into a values ??(‘zhao’, 24); 
14:42:01 SQL> commit; 
14:42:03 SQL> select a. *, Ora_rowscn from a;

NAME AGE ORA_ROWSCN 
—————————— 
500651577059 
600651577059 
111101577059 
2221001577059 
333301577059 
444401577059 
555501577059 
888341573035 
zhao 24 1585133 
– A: SCN check the record of the previous version 
14:42:18 SQL> select * from a as of scn 1577059;

NAME AGE 
——————– 
50065 
600 65 
11110 
222 100 
33330 
44440 
55550 
88834 
– B: timestamp check the record of the previous version 
14:42:51 SQL> select * from a as of timestamp scn_to_timestamp (1577059);

NAME AGE 
——————– 
50065 
600 65 
11110 
222 100 
33330 
44440 
55550 
www 11 
88834 
> Audit function: 
14:50:06 SQL> select a. *, Versions_operation, versions_xid, versions_starttime from a 
2 versions between timestamp minvalue and maxvalue;

NAME AGE VERSIONS_OPERATION VERSIONS_XID VERSIONS_STARTTIME 
————————————————– ———————— 
600 65 
11110 
222 100 
33330 
44440 
55550 
88834 
zhao 24 I 0500110006030000 27-4 -07 pm on 02.42.00 
 Can be seen in the 27-4 -07 pm on 02.42.00 insert (i) a record

14:50:53 SQL> a order byversions_starttime; 
14:51:23 SQL> run;

NAME AGE VERSIONS_OPERATION VERSIONS_XID VERSIONS_STARTTIME 
————————————————– ————————– 
50065 
600 65 
11110 
222 100 
33330 
44440 
55550 
88834 
zhao 24 I 0500110006030000 27-4 -07 pm on 02.42.00 
: Oracle falshback transaction query Flashback Transaction Query 
oracle10 flashback transaction feature provides query and revocation of completed transactions over a certain period of time! 
14:59:56 SQL> select undo_sql, table_name from flashback_transaction_query x where x.table_owner = upper (‘zxt’) and x.xid = ‘0500110006030000 ‘;

UNDO_SQL TABLE_NAME 
————————————————– ——————————————– 
delete from “ZXT”. “A” where ROWID = ‘AAANOsAAGAAAAMWAAA’; A 
– UNDO_SQL Table A DML operation; 
Five: Oracle falshback Database: Flashback Database 
oracle10g the Flashback database level, can make the entire database restore data to a point in time or SCN!Involved batch operation of the database, this knowledge is temporarily unable to study!