Recover a dropped table in Oracle 11g

How to recover a dropped table in Oracle 11g when flashback mode is off

After dropping table and before restoring it from the recycle bin, run the following query:

Login with table owner schema.

SQL > SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

if table is there than use below statement to restore.

Restore the table with the following command:

SQL > FLASHBACK TABLE drop_table_NAME TO BEFORE DROP;

Recovering dropped table is easy in Oracle, provided that the table was not dropped with PURGE option. In case the table is dropped and space occupied by the table is released and the table does not get moved into the recycle bin. But if table is dropped without PURGE option, Oracle has this very neat feature - Recycle bin, similar to the recycle bin in Windows. There are two recyle bin views in Oracle: USER_RECYCLEBIN and DBA_RECYCLEBIN, Synonym RECYCLEBIN points to your USER_RECYCLEBIN.

The recycle bin can be turned on and off with RECYCLEBIN initialization parameter. When table is dropped, it get rename to system-generated name preceeded with BIN and stored in recycle bin. The important thing to know is that after table has been dropped, it's only been renamed, the table segmants are still in the tablespace, unchanged. the space occupied by the table will not be reclaimed until the table has been purged from the recycle bin.

While in the recycle bin, the table can even be queried using the newly generated name that starts qwith BIN$.

The table can easily be recovered from the recycle bin using flashback drop, which will rename the table to its original name.

You can check flashback mode by running

SELECT FLASHBACK_ON FROM V$DATABASE;

First check the parameter Recyclebin is set to true. Recycle bin is a data dictionary table that contains 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.

SHOW PARAMETER RECYCLEBIN;

if recyclebin is set to off, perform the following steps:


ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;

verify that recyclebin parameter is now set to ON

shutdown the database
SHUTDOWN IMMEDIATE

Restart the database
STARTUP

then run
SELECT * FROM RECYCLEBIN;

and see if your table is in there. If it is, use the following quesry to restore it:
FLASHBACK TABLE  TO BEFORE DROP;

Then check if the table is back:
SELECT * FROM USER_TABLES WHERE TABLE_NAME=;

**********************************************************

SQL >  SELECT * FROM RECYCLEBIN;

SQL >  SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'Schema_name';


SQL >  SELECT object_name, original_name, createtime FROM recyclebin;

SQL >  desc dba_recyclebin;

SQL >  select OWNER,OBJECT_NAME,ORIGINAL_NAME,CREATETIME,DROPTIME from dba_recyclebin WHERE owner = 'Schema_name';

Login with drop table owner schema

SQL > show user;

HR

SQL > FLASHBACK TABLE DROP_TABLE_NAME TO BEFORE DROP;



Clear Recyclebin

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;
To remove all dropped objects from the recyclebin (system wide, available to SYSDBA only or, starting with version 12c, to users having the PURGE DBA_RECYCLEBIN system privilege):

PURGE DBA_RECYCLEBIN;
Tables can also be dropped without sending them to the recyclebin. Example:

DROP TABLE tba PURGE;
Tables inside recycle bin can be purged individually. Example:

PURGE TABLE tba;

Drop a table:

SQL> DROP TABLE tba;
Undrop the table:


SQL> FLASHBACK TABLE tba TO BEFORE DROP;

ORA-48913: Writing into trace file failed, file size limit

Error detailed from alert log.

Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
ORA-609 : opiodr aborting process unknown ospid (4397_47562687353296)
ORA-609 : opiodr aborting process unknown ospid (28545_47989084123600)
ORA-609 : opiodr aborting process unknown ospid (4703_47579510747600)
Non critical error ORA-48913 caught while writing to trace file "/u01/prod/db/tech_st/11.1.0/admin/PROD1_prod1/
diag/rdbms/prod1/PROD1/trace/PROD1_dbrm_13082.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached


Cause :-

We can increase the setting for the parameter MAX_DUMP_FILE_SIZE or set it to unlimited

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.
A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.




Solution :-


SQL> show parameter MAX_DUMP_FILE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      20480
SQL>

You would need to reset value of MAX_DUM_FILE_SIZE in order to have your trace files size extend beyond 20480 MB now. You can use “alert system” 
command to change the value. You may also set it to UNLIMITED.


SQL> ALTER SYSTEM SET max_dump_file_size=unlimited;