To start, I am a student and just getting my feet wet with Oracle.

Quick history

  • Virtual Box XP running Oracle express with SQL Developer.
  • Created a user and tablespace for myself. (Key here is I'm not on System)
  • I have checked to make sure my Recycle Bin is active with

    SHOW Parameter recyclebin;


  • I have also tried alter session to on.

Long story short Create table, add some data, drop table with standard drop command with no purge at the end and table does not go to recycle bin and hence can't use the flashback command to retreive it. Any idea as to why my recycle bin would not be functioning as it should, or am I missing something obvious? I am very green so assuming the latter :) Any feedback would be appreciated!

  • Try a "show recyclebin;" What is returned?
    – Aaron
    Feb 13, 2012 at 5:28
  • 2
    Is the flashback command you refer to = flashback table TABLE_NAME to before drop? Also, if you delete the table, I wouldn't assume the table going to the recycle bin. The table goes there if you drop the table. It really would help if you gave us the exact SQL statements you typed in as well as potential error messages returned by Oracle. Feb 13, 2012 at 6:10
    – jreed72
    Feb 13, 2012 at 7:05
  • keep in mind this is very basic as I'm just learning the syntax. CREATE TABLE PROVINCE_ID (ID NUMBER(10) , PROVINCE VARCHAR2(30)); DROP TABLE PROVINCE_ID; FLASHBACK TABLE PROVINCE_ID TO BEFORE DROP; these are the 3 steps I'm doing to try and make sure the recycle bin is working.
    – jreed72
    Feb 13, 2012 at 7:13
  • The show recyclebin; command does not do anything, at least nothing I can see...
    – jreed72
    Feb 13, 2012 at 7:13

3 Answers 3


Well with some digging on the Doc for Oracle 11g Express, it appears the flashback functionality is not supported. So this would be a good indication of why I'm having problems!!

  • Can you provide a reference? My own brief confirms your findings, but I can't find a reference to back it up. The "Oracle® Database Express Edition 2 Day DBA 11g Release 2 (11.2)" at docs.oracle.com/cd/E17781_01/server.112/e18804/… has a whole section on recovering dropped tables. The product editions page (oracle.com/us/products/database/product-editions-066501.html) explicitly says that Flashback Query is available, but Flashback Table, Flashback Database, and Flashback Transaction Query are not available, although it says nothing about Flashback Drop. Feb 14, 2012 at 15:52
  • docs.oracle.com/cd/E17781_01/license.112/e18068/… is what I was looking at. However it does not refer to flashback drop either. I assumed flashback table was the same feature or at least tied together. Now I'm not so sure...
    – jreed72
    Feb 14, 2012 at 17:38
  • I'm not so sure either, particularly because it is working now on my system. See my answer for more details. Feb 15, 2012 at 21:58

Flashback Drop does work as implied by the documentation for the Express Edition of 11g, but the default configuration needs to be modified to get it to work.

By default XE isn't in ARCHIVELOG mode as shown by...

SELECT log_mode FROM v$database;

If the query does not return ARCHIVELOG then follow these instructions to turn it on.

The following expert is from found in 10g Backup and Recovery Basics (strange place, I know) in a section called Limitations and Restrictions on Flashback Drop:

The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.

Apparently the default tablespace in XE is SYSTEM as seen by the following:

SELECT property_value FROM database_properties WHERE property_name='DEFAULT_PERMANENT_TABLESPACE';

To determine what the tablespace is for the user you are logged in as, run the following:

SELECT user, default_tablespace FROM dba_users WHERE username=user;

If the user is a built in account, then you will need to create a new user and assign it to a different tablespace such as USERS. If your user is already not a built in account, but is using the SYSTEM tablespace, then simply change the default tablespace as follows:

ALTER USER myusertoalter QUOTA UNLIMITED ON users;

I tried your Scenario, It works.. just see what i did:-

--create table

select * from user_tables; -- to chk if the table was created and is listed

DROP TABLE PROVINCE_ID; -- Drop the table now

select * from recyclebin; -- see it in recycle bin as some BINXYZ... name


select * from recyclebin;-- should be empty if no other tables are there
  • 1
    I wonder if this is something specific to Express version? Some of my other classmates installed full version and seems to work fine for them... azzaxp are you running full install or just Express?
    – jreed72
    Feb 13, 2012 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.