Dropped tables are not going to Recycle Bin

Question

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;

  command.

• 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!

Answer 1

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!!

Answer 2

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

--create table
CREATE TABLE PROVINCE_ID (ID NUMBER(10) , PROVINCE VARCHAR2(30));

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

FLASHBACK TABLE PROVINCE_ID TO BEFORE DROP; -- Flash back

select * from recyclebin;-- should be empty if no other tables are there

Answer 3

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 DEFAULT TABLESPACE users;
ALTER USER myusertoalter QUOTA UNLIMITED ON users;