How to get back dropped Tablespace using Flashback Database.

It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.

Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.

SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99;Table created.

2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686

SQL> drop tablespace test_restore including contents;
Tablespace dropped.

3)Mount the database.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Perform FlashBack.

SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

5)The datafile Become Unnamed. So rename it with original data file location.

SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.

6)Now perforem Flashback and Open the database with read only mode.

SQL> flashback database to scn 938686;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> select count(*) from test;
COUNT(*)
----------
98


7)Now you can follow the step 6 choice b)in Peforming Flashback (export) and then recover database. And later create tablespace and import the contents of tablespace.

Related Document:
-------------------
Perform Flashback Database

Limitations of Flashback Database