Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, September 16, 2012

ORA-38305: object not in RECYCLE BIN

Perils of having recyclebin turned off in your database.

For me it was a requirement to have the recyclebin turned off, since my application creates temporary staging tables and drops them daily and I cannot afford to keep them in my database.
If  you have recyclebin turned off at the database level then the dropped table will no longer reside in the database i.e you cannot flashback the table as the table segments will be dropped ( un allocated and released to database for reuse)

SQL>show parameter recyclebin

recyclebin                           string      OFF



SQL> create table test123 tablespace users  as select * from dba_tables ;

Table created.

SQL> commit;

Commit complete.



SQL> select count(*) from test123 ;

  COUNT(*)
----------
      3068

SQL> drop table test123;

Table dropped.

SQL> select OWNER,OBJECT_NAME from dba_recyclebin;

no rows selected


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

No comments: