FLASHBACK TECHNOLOGY IN ORACLE

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;