Flashback - 10g or Later

Today i need to post on flashback feature on oracle database (10g or later)

Version: 10.2.0.1

One of my friend needed step by step configuration of Flashback. Here we go..


Pre-Requisites:
1. Database should be on archivelog mode

Startup the database with mount option:
(Simple Example of the procedure)

connect as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 14:40:28 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 184553284 bytes
Database Buffers 411041792 bytes
Redo Buffers 7163904 bytes
Database mounted.

Enable the Flashback:

SQL> alter database flashback on;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1G;

System altered.

SQL> alter database open;


Now connect as a normal user who has default tablespace as someone other than SYSTEM

SQL> conn balaji/balaji
connected

SQL> create table test (id number(2), name varchar(30));

Table created.

SQL> insert into table test values (3, 'INDIA');
1 row inserted

SQL> insert into table test values(4,'SRILANKA');
1 row inserted

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL> select tablespace_name from dba_segments where segment_name like 'TEST';

TABLESPACE_NAME
------------------------------
USERS

Note the user balaji has the default tablespace as USERS.

Drop the table test:

SQL> drop table test;

Table dropped.


SQL> select * from test;
ERROR at line 1:
ORA-00942: table or view does not exist


Using Flashback to get the table from recycle bin:

SQL> flashback table "TEST" to before drop;

Flashback complete.

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL>

Points to remember:
Flashback option will not work for SYSTEM tablespace objects.

You will receive:
SQL> flashback table "TEST" to before drop;
flashback table "TEST" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

This is not about SYS objects, this is about SYSTEM tablespace... I can create a sys.table (logged as sys) on USER tablespace and then recover it through flashback.

Recycle functionality is available only for non-system locally-managed tablespaces.

Hope it helps... Cheers!!!