Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Try a "show recyclebin;" What is returned? –  BryceAtNetwork23 Feb 13 '12 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. –  René Nyffenegger Feb 13 '12 at 6:10
    
CREATE TABLE PROVINCE_ID –  jreed72 Feb 13 '12 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 '12 at 7:13
    
The show recyclebin; command does not do anything, at least nothing I can see... –  jreed72 Feb 13 '12 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!!

share|improve this answer
    
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. –  Leigh Riffel Feb 14 '12 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 '12 at 17:38
    
I'm not so sure either, particularly because it is working now on my system. See my answer for more details. –  Leigh Riffel Feb 15 '12 at 21:58

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
share|improve this answer
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 '12 at 17:47

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;
share|improve this answer
    
Thanks Leigh I will try this out and let you know –  jreed72 Feb 20 '12 at 0:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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