Only FLASHBACK DATABASE requires flashback to be ‘ON’ since only FLASHBACK DATABASE uses flashback logs in the flash_recovery_area.

All other forms of FLASHBACK use the recycle bin and undo_tablespace.

Important info:

You cannot ‘flashback table to before drop’ a table which has been created in the SYSTEM tablespace.

The table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace and that tablespace must be locally managed.

When you drop a table, the objects are temporarily placed in a ‘recycle bin’ and still belong to the owner.

The space used by recycle bin objects is never reclaimed unless there is space pressure.

The space associated with the dropped object is not immediately reclaimable although it appears in the DBA_FREE_SPACE view.

Query the dba_recyclebin view as SYS or just recyclebin as the user for information about the recycle bin.

Flashback drop allows you to recover a dropped table.

Example.

Connect arjun/arjun

Create table tempp (col_1 number(10)) tablespace users;

Insert into tempp values (10);

1 row created.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI2Gp/QiZsPgQAw5yUImEA==$0 TABLE 2005-09-12:10:49:16

SQL> flashback table tempp to before drop;

Flashback complete.

SQL> select * from tempp;

COL_1
———-
10

In case the table is created in the system tablespace :

SQL> show user

USER is “SYS”…..Default tablespace SYSTEM

SQL> create table arjun1 (col_1 number(10));

Table created.

SQL> drop table arjun1;

Table dropped.

SQL> select * from dba_recyclebin;

no rows selected

SQL> flashback table arjun1 to before drop;
flashback table arjun1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Permanently drop without recycle bin
Drop table test purge;

QUERYING DROPPED TABLES

Dropped tables can be queried from the recycle bin. No DML or DDL operations are allowed on the table.

SQL> drop table tempp;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI9AwvFRdf7gQAw5yUIsGA==$0 TABLE 2005-09-12:13:15:22

While querying the recycle bin, make sure the system generated table name is enclosed in double quotes.

SQL> select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0;
select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0’;
select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0’
*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from “BIN$AI9AwvFRdf7gQAw5yUIsGA==$0”;

COL_1
———-
10

You cannot run any DML or DDL on dropped tables ….

SQL> Insert into tempp values(20);
Insert into tempp values(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist

FLASHBACK TABLE TO A TIME IN THE PAST.

Firstly enable row movement for table test..

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

TIME : 08:00:00

SQL> select * from test;

SALARY
———-
5000

TIME :08:00:01

SQL> update test set salary =6000;

1 row updated.

SQL> select * from test;

SALARY
———-
6000

SQL> commit;

Commit complete.

Now flashback table to time 08:00:00

SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( ‘2005-09-13 08:00:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> SELECT * FROM TEST;

SALARY
———-
5000