Recyclebin

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

Yes, you would need to set the recyclebin on:

Code:

---

ALTER SYSTEM SET RECYCLEBIN = 'ON';

---

:rolleyes:

Quote:

---

Originally Posted by LKBrwn_DBA

Yes, you would need to set the recyclebin on:

Code:

---

ALTER SYSTEM SET RECYCLEBIN = 'ON';

---

:rolleyes:

---

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

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

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

Quote:

---

Originally Posted by 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

---

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?

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

Quote:

---

Originally Posted by davey23uk

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...

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

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

Quote:

---

Originally Posted by 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

---

BORE,
You are soooooooooooooo right. I just wanted to test the feature so I just went in as SYSTEM and of course was doing all that stuff on SYSTEM tbs. Now I went in as an ordinary schema fella and whalaa!!!...thanks a bundle...

are you doing your tests loggin into the system as SYSTEM?

You are either a very dangerous or a very lucky person... or both