Recyclebin

[ndisang]

I dropped a table...then i attempted to bring it back with flashback to before drop...but i get the following...
SQL> flashback table tony to before drop;
flashback table tony to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

I thought Oracle10g (my version) stores a copy in the recyclebin by default? Do you do anything to enable this action?
thanks

[LKBrwn_DBA]

Yes, you would need to set the recyclebin on:

ALTER SYSTEM SET RECYCLEBIN = 'ON';

[ndisang]

Yes, you would need to set the recyclebin on:

ALTER SYSTEM SET RECYCLEBIN = 'ON';

thanks....i suspected as much.....

[ndisang]

know what? still didn't work...

SQL> ALTER SYSTEM SET RECYCLEBIN = 'ON';

System altered.

SQL> create table tony (ssn number(9), name varchar2(6));

Table created.

SQL> drop table tony;

Table dropped.

SQL> SELECT object_name, original_name FROM recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$KijYSqTdCvTgRAAUTyMt/w==$0 LOGSTDBY$SKIP_SUPPORT

=====was expecting to see a reference to table TONY...

SQL> flashback table tony to before drop;
flashback table tony to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

[dbasan]

double check!! It works

Check list:
> DB_FLASHBACK_RETENTION_TARGET set
> FLASHBACK database ON
> check SELECT FLASHBACK_ON FROM V$DATABASE; --Yes
> RECYCLEBIN ON
> create table, drop and flash back --should work

[ndisang]

double check!! It works

Check list:
> DB_FLASHBACK_RETENTION_TARGET set
> FLASHBACK database ON
> check SELECT FLASHBACK_ON FROM V$DATABASE; --Yes
> RECYCLEBIN ON
> create table, drop and flash back --should work

Thanks
I see the issue is with the flsah back not coming on....

SQL> sho parameter retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

SQL> ALTER SYSTEM SET RECYCLEBIN = 'ON';

System altered.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO <-----you would expect this value to be YES but why no?

[davey23uk]

because it was either turned off or never turned on, you need to turn it on in the mount stage

[ndisang]

because it was either turned off or never turned on, you need to turn it on in the mount stage

Thanks davey....
but when you do

SQL> ALTER SYSTEM SET RECYCLEBIN = 'ON';

System altered.

...and get "SYSTEM ALTERED", doesn't it mean that this parameter is modifiable on the go? Else why doesn't it fail and ask you to do it at mount time?

again thanks...

[davey23uk]

i was talking about flashback database (im not even totally sure you need flashback on for recycle bin though)

[Bore]

See, there are some exceptions of the recycle bin policy.
Not sure, but I belive objects of the SYSTEM tablespace are not covered by the recycle bin. Also Dictionary managed tablespaces are not covered. So check if u do not create objects in the system tablespace, and also that the tablespace u use is locally managed. Or better create a new locally managed tablespace, create a new table there, drop it and then see if it operates

Regards
Boris