Flashback feature actually we can go in past to see and recover data .
flashback use undo data
*Flashback Query
*Flashback Version Query
*Flashback Transaction Query
*Flashback Table
*Flashback Drop (Recycle Bin)
Flashback Query:
SQL>CREATE TABLE test (id NUMBER(10));
SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
SQL>SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
195300
2014-03-12 13:11:56
SQL>insert into test (id number(10));
SQL> commit
SQL>select * from test;
SQL>select * from test as of TIMESTAMP TO_DATE('2014-03-12 13:06:22','YYYY-MM-DD HH24:MI:SS')
SQL>select * from test as of TIMESTAMP TO_DATE('2014-03-12 13:02:00','YYYY-MM-DD HH24:MI:SS')
SQL>select * from test as of scn 195300
Flashback Version Query:
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.
SQL>create table test (id number,name varchar2(30));
SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
195454
2014-03-12 13:26:00
SQL>insert into test values('&id','&name');
1 anurag
SQL>commit;
SQL>update test set name='a' where id=1;
SQL>commit;
SQL>update test set name='b' where id=1;
SQL>commit;
SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
195641
2014-03-12 13:30:00
SQL>SELECT versions_startscn,versions_starttime,
versions_endscn,versions_endtime,
versions_xid,versions_operation,name
FROM test
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2014-03-12 13:25:00','YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2014-03-12 13:30:00','YYYY-MM-DD HH24:MI:SS')
WHERE ID=1;
About
pseudocolumn details
VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.
VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
VERSIONS_XID - ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)
Flashback Transaction Query:
Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.
The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.
SQL>SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('05001B0029000000');
Flashback Table:
The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.
You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).
SQL>FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-03-12 13:26:00', 'YYYY-MM-DD HH24:MI:SS');
SQL>alter table test enable row movement
SQL>select * from test
Flashback Drop (Recycle Bin):
SQL> create table test (id number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> drop table test;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$9GaQ+m3ZBzngROQfE7OXKA==$0 TABLE 2014-03-12:15:06:08
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$9GaQ+m3ZBzngROQfE7OXKA==$0 TABLE
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
ID
----------
1
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.
Several restrictions apply relating to the recycle bin.
Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.
Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.
Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential integrity.
//if default tablespaece is system
if default tablespace not exist then by default system tablespace is default tablespace for any new user.
if default tabelespace system then recyclebin not working
//make non system tablespace for recycelbin
This feature can be disabled/enabled at the session or system level.
-- Session level.
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;
-- System level.
ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;
