FLASHBACK TABLE
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
Oracle DBA Tips and Techniques 