3

Just a general question about flashback and the recycle bin in Oracle. I tried doing this on a demo oracle database I have but the recycle bin does not seem to want to be populated; and I want a quick answer.

One can do the following:

flashback table table_name to before drop;
alter index "BIN$U/9fvJKaASzgQKjAYAIWhw==$0" rename to sys_c1113050;

Can you do this then, to restore a table:

RENAME TABLE BIN$U/9fvJKaASzgQKjAYAIWhw==$0 TO TABLE_NAME
  • 1
    Try google: "oracle restore table from recycle bin". 4th result is a very good description on how to deal with the recyclebin. – user56901 Jan 21 '15 at 14:50
  • 1
    Sounds like a bad idea. If the RecycleBin functions are not feature-rich enough, what you can do is create your own Recycle Bin. That way, you can restore back again, like you are wanting to do (with the objects now in the system's RecycleBin). All you need to do is create a separate schema, or even tablespace, put nothing in it you've at least considered dropping, and occasionally drop everything in it. But, that would allow "un-delete" by means of just renaming it again. This on condition you didn't purge anything in it yet. – tvCa Jan 21 '15 at 17:16
6

No you cannot. To restore a table from the recycle bin use flashback table <table_name> to before drop;

<table_name> is the original table name and not the object_name from the recycle bin.

Do not rename the table to restore it from the recycle bin!

  • Thanks for the answer ora-600. From your answer I get the impression that you can rename the BIN$... table name to the original <table_name> but it would be bad practise; correct? – Stelios Jan 21 '15 at 14:57
  • 2
    I was not 100% sure if you can rename a table from the recycle bin. I confirmed it in a test: You can not. If you could do it I would not recommend it. – ora-600 Jan 21 '15 at 15:10
  • 3
    alter table "BIN$DSeKsKbPUivgUygeqMBdyQ==$0" rename to test_rest; will result in ORA-38301 can not perform DDL/DML over objects in Recycle Bin – ora-600 Jan 21 '15 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.