Re: Unable to recover a drop table from recyclebin?

On May 2, 3:02 am, DA Morgan <damor..._at_psoug.org> wrote:
> Maxim Demenko wrote:
> > DA Morgan schrieb:
>
> >> FLASHBACK TABLE "<recyclebin_name_not_original_name>
> >> TO BEFORE DROP;
>
> > ???
>
> > Best regards
>
> > Maxim
>
> Does this help?
>
> SQL> create table t (
> 2 testcol varchar2(20));
>
> Table created.
>
> SQL> drop table t;
>
> Table dropped.
>
> SQL> select object_name, original_name
> 2 from recyclebin;
>
> OBJECT_NAME ORIGINAL_NAME
> ------------------------------ --------------------------------
> BIN$PiPfN+acQVy42vRuWDgYmQ==$0 T
>
> SQL> flashback table "BIN$PiPfN+acQVy42vRuWDgYmQ==$0"
> 2 to before drop;
>
> Flashback complete.
>
> SQL> desc t
> Name Null? Type
> ----------- ------------ ------------
> TESTCOL VARCHAR2(20)
>
> SQL>
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Quote from the docs:

--
You can specify either the original user-specified name of the table
or the system-generated name Oracle Database assigned to the object
when it was dropped.

System-generated recycle bin object names are unique. Therefore, if
you specify the system-generated name, then the database retrieves
that specified object.

If you specify the user-specified name, and if the recycle bin
contains more than one object of that name, then the database
retrieves the object that was moved to the recycle bin most recently.
--

However, as Maxim noted, system tables can't be recovered with
FLASHBACK TABLE, and this includes all user tables created in system
tablespace (which should never be there to start with.)

Regards,
   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)
   http://www.dynamicpsp.com