Skip to content

can we recover dropped table from recyclebin if flashback mode off

yes ,We can recover dropped table from recyclebin even flashback database mode is disabled.

What is Recycle Bin?

recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

  • When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
  • When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
  • When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> alter system set recyclebin=ON scope=spfile;

System altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      ON
SQL> conn testuser/testuser;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ABC                            TABLE
ABCD                           TABLE
XXX                            TABLE

SQL> create table test(empno number);

Table created.

SQL> insert into test values(001);

1 row created.

SQL> insert into test values(002);

1 row created.

SQL> insert into test values(003);

1 row created.

SQL> insert into test values(004);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> drop table test;

Table dropped.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set lines 500
SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN
------------------------------ -------------------------------- ---------
BIN$nkFizCIuTY23wgOmv1B1vA==$0 TEST                             2011-07-03:13:27:35    8811423

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

no rows selected

SQL>

Ref : http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables011.htm#i1011362

4 Comments Post a comment
  1. A very well written article and keep up the good work Azar.

    July 3, 2011
  2. charly #

    Can you help me? i dont really understand why you say you can drop a able and after do a flashback after drop to get back the table in the database. It´s not working. I am connected as sys user. Here the following example:

    SQL> insert into test_flashback values (1);

    1 row created.

    SQL> insert into test_flashback values (2);

    1 row created.

    SQL> insert into test_Flashback values (3);

    1 row created.

    SQL> insert into test_flashback values (4);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from test_flashback;

    EMPNO
    ———-
    1
    2
    3
    4

    SQL> truncate table test_flashback;

    Table truncated.

    SQL> select * from test_flashback;

    no rows selected

    SQL> commit;

    Commit complete.

    SQL> drop table test_Flashback;

    Table dropped.

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

    SQL> commit;

    Commit complete.

    SQL> select * from recyclebin;

    no rows selected

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

    October 19, 2012

Trackbacks & Pingbacks

  1. [A] Recover dropped table from recycle bin… | Oracle in Thai | Oracle in Thai | Oracle User Group in Thailand |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: