Re: Unable to recover a drop table from recyclebin?

lazyboy_2k_at_yahoo.com wrote:
> Hi,
>
> I'm trying to find out why I'm not able to recover a drop table from a
> recyclebin where a recyclebin is already turned on. I'm running sol
> 10 & oracle 10g. Am I missing anything?
>
> SQL> alter session set recyclebin = on;
>
> Session altered.
>
> SQL> select count(*) from recyclebin;
>
> COUNT(*)
> ----------
> 0
>
> SQL> create table mytable as select * from student where s_last =
> 'Johnson' and s_first = 'Mike';
>
> Table created.
>
> SQL> select * from mytable;
>
> S_ID S_LAST S_FIRST
> ---------- -------------------- -------------------- -
> S_ADDRESS S_CITY S_ S_ZIP
> S_PHONE S_
> ------------------------------ -------------------- -- --------
> ---------- --
> S_DOB S_PIN F_ID
> --------- ---------- ----------
> TIME_ENROLLED
> ---------------------------------------------------------------------------
> JM100 Johnson Mike
> 688 4th St. Orlando FL 34158
> 7155554944 FR
> 04-DEC-87 9188 3
> +00-04
>
>
> SQL> show parameters recyclebin
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> recyclebin string ON
>
> SQL> drop table mytable;
>
> Table dropped.
>
> SQL> select count(*) from recyclebin;
>
> COUNT(*)
> ----------
> 0
>
> SQL> flashback table mytable to before drop;
>
> flashback table mytable to before drop
> *
> ERROR at line 1:
> ORA-38305: object not in RECYCLE BIN
>
>
> SQL>
> SQL> show parameters recyclebin
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> recyclebin string ON
>
> Any helps/suggestions are appreciated.
> TIA,
> -Chris

Because your syntax is incorrect.

SELECT object_name, original_name
FROM recyclebin;

FLASHBACK TABLE "<recyclebin_name_not_original_name> TO BEFORE DROP; Look at the demos in Morgan's Library at www.psoug.org under RECYCLEBIN and TABLE FLASHBACK: They work!

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org