CODEWITHSUNDEEP
sqloracle-database
ken.ng
ken.ng

Reputation: 241

Dropped table became permanently deleted

I'm new to this Oracle Database. Today I ran DROP TABLE table1; and tried to FLASHBACK it. But the Script Output returned this :

FLASHBACK TABLE TABLE1 TO BEFORE DROP
Error report -
ORA-38305: object not in RECYCLE BIN
38305. 00000 - "object not in RECYCLE BIN"
*Cause: Trying to Flashback Drop an object which is not in RecycleBin.
*Action: Only the objects in RecycleBin can be Flashback Dropped.

I thought that the recyclebin was somehow disabled. So I opened another connection and input this command :

ALTER SESSION SET recyclebin = ON;

and repeated the process again, the result was still the same. There was nothing in the recyclebin when I ran SELECT * FROM RECYCLEBIN;

Did I unintentionally mess up anything ?

Upvotes: 0

Views: 3209

Answers (3)

Duong
Duong

Reputation: 511

The technique you're using is Oracle Flashback Drop. It's enabled when satisfying the following three conditions:

  1. Parameter RECYCLEBIN='on'
  2. Data not being stored in SYSTEM tablespace
  3. Data must be stored in a locally managed tablespace

I guest that you're simulating your examples under SYS user (which has the default tablespace SYSTEM) leading to you can't flachback at all. I did try to login as SYS user and simulating a small example as yours and do get the same error output as yours.

Try your lab under another user which default tablespace not SYSTEM. You can verify the conditions by checking:

  1. Recleclebin='on' with SQL*PLUS SQL> SHOW PARAMETER RECYCLEBIN;
  2. Default Tablespace not SYSTEM with SQL> select default_tablespace from dba_users where username='input_username';
  3. Tablespace datafile is locally managed with SQL>select extent_management from dba_tablespaces where tablespace_name='input_tablespace_name';

Upvotes: 3

J-Alex
J-Alex

Reputation: 7127

Oracle Flashback Drop reverses the effects of a DROP TABLE operation. It can be used to recover after the accidental drop of a table. Flashback Drop is substantially faster than other recovery mechanisms that can be used in this situation, such as point-in-time recovery, and does not lead to any loss of recent transactions or downtime.

The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. You can explicitly purge a table or other object from the recycle bin with the command:

DROP TABLE some_table PURGE;

Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace. This condition is referred to as space pressure. Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota on it.

When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.

There is no fixed amount of space preallocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the recycle bin.

To view only objects in the recycle bin, use the USER_RECYCLEBIN and DBA_RECYCLEBIN views.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

you didn't mess up anything with the command ALTER SESSION SET recyclebin = ON; but it's late to invoke that command. I think your problem is due your default tablespace to be SYSTEM for tables.

Upvotes: 0

Related Questions