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
DBA 