-
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
Life is what is happening today while you were planning tomorrow.
-
Yes, you would need to set the recyclebin on:
Code:
ALTER SYSTEM SET RECYCLEBIN = 'ON';
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by LKBrwn_DBA
Yes, you would need to set the recyclebin on:
Code:
ALTER SYSTEM SET RECYCLEBIN = 'ON';
thanks....i suspected as much.....
Life is what is happening today while you were planning tomorrow.
-
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
Life is what is happening today while you were planning tomorrow.
-
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
-
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?
Life is what is happening today while you were planning tomorrow.
-
because it was either turned off or never turned on, you need to turn it on in the mount stage
-
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...
Life is what is happening today while you were planning tomorrow.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|