patternMinor
Dropped table not showing up in recycle bin
Viewed 0 times
recycleshowingbindroppednottable
Problem
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 ?
[update]:
i had tried
Its VALUE column shows it as ON . So fine here !
Then i droppped an existing table but still
the command
and any such retrieval of columns is showing
Also using
So where are dropped tables going ?
And one more thing :
This time i tried using
Instead of
So, is this because that i am working as a SYSTEM ??
i have not created any other USER as of now .
What's the deal ?
[update]:
i had tried
SHOW PARAMETER RECYCLEBIN;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
ALTER SYSTEM SET RECYCLEBIN=ON;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 .
Solution
It's likely that recyclebin isn't turned on. Check in your init.ora file to ensure it is. You can check your parameter:
And turn it on at session level...
...and then try a drop.
Also, check both
EDIT: I see you've updated to say you are logged in as the
select *
from V$PARAMETER
where NAME = 'recyclebin';And turn it on at session level...
ALTER SESSION SET RECYCLEBIN=ON;...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.Code Snippets
select *
from V$PARAMETER
where NAME = 'recyclebin';ALTER SESSION SET RECYCLEBIN=ON;Context
StackExchange Database Administrators Q#11343, answer score: 4
Revisions (0)
No revisions yet.