I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it . What's the deal ?


i had tried


Its VALUE column shows it as ON . So fine here !

Then i droppped an existing table but still the command

    Select object_name from recyclebin;

and any such retrieval of columns is showing NO ROWS SELECTED !!

Also using flashback command confirms that recyclebin is not having any such dropped tables !!

So where are dropped tables going ?

And one more thing :

This time i tried using


Instead of Alter session and sqlplus throws an error :

Specified initialization parameter is not modifiable with this option

So, is this because that i am working as a SYSTEM ?? i have not created any other USER as of now .

2 Answers 2


It's likely that recyclebin isn't turned on. Check in your init.ora file to ensure it is. You can check your parameter:

select *
where  NAME = 'recyclebin';

And turn it on at session level...


...and then try a drop.

Also, check both USER_RECYCLEBIN and DBA_RECYCLEBIN for entries.

EDIT: I see you've updated to say you are logged in as the SYSTEM user. For some reason, flashback/recyclebin does NOT work for SYS. I can't find a good reason why but it just doesn't. Create a new user and try creating and dropping a table while logged in as them. I'm sure you'll find that the recyclebin works fine then.

  • i cant find any relevant entries in this init.ora file for recycle bin and i ran the command but in that too it seems nothing that i can do with it !!
    – kushal
    Jan 20, 2012 at 6:59
  • Updated my answer to mention that flashback won't work in the SYSTEM tablespace.
    – John Doyle
    Jan 20, 2012 at 15:38
  • yeah ,man just came to update the same . But you already did.
    – HalfWebDev
    Jan 22, 2012 at 17:28
  • and i still don't understand the first command up there . Its neither working as SYSTEM user nor as any other user. So please edit it .
    – HalfWebDev
    Jan 22, 2012 at 17:30

It sounds like the recycle bin is on and should be working. The only thing I can think of is that you are using the PURGE option on your DROP commands -- is that the case?

The error you got from the ALTER SYSTEM command means exactly what it says -- the RECYCLEBIN option cannot be changed in that manner. According to the documentation, you need to use the DEFERRED option, meaning that it will take effect for any new sessions but not for currently active ones. But in any case, it sounds like the parameter is already on.

  • Indeed It is and i am not even touching PURGE as of now .
    – kushal
    Jan 20, 2012 at 13:15

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.