DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Recyclebin

  1. #1
    Join Date
    Sep 2001
    Posts
    200

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    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

  3. #3
    Join Date
    Sep 2001
    Posts
    200
    Quote 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.

  4. #4
    Join Date
    Sep 2001
    Posts
    200
    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.

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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

  6. #6
    Join Date
    Sep 2001
    Posts
    200
    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?
    Life is what is happening today while you were planning tomorrow.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    because it was either turned off or never turned on, you need to turn it on in the mount stage

  8. #8
    Join Date
    Sep 2001
    Posts
    200
    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...
    Life is what is happening today while you were planning tomorrow.

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    i was talking about flashback database (im not even totally sure you need flashback on for recycle bin though)

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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
  •  


Click Here to Expand Forum to Full Width