Restoring Table From Recyclebin

Posted: March 14, 2013 in Database General
Tags: , ,

Recently there is situation where user has dropped around 25 tables from a test environment and i need to recover those tables. Since, recyclebin is “ON”, it seems to be a easy task. Of couse, recovery of table is easy but recovery of dependent is a real pain. On checking, i see that there are about total 113 objects which got dropped. It includes (Tables, Triggers, Indexes). Here are few lines and query against my recyclebin.


SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
------------------------------ ------------------- ------ ------------------- ---
BIN$12jgaeSlAJLgQwotC20Akg==$0 PROJ_CREATED_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSkAJLgQwotC20Akg==$0 PROJECT_NAME_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeScAJLgQwotC20Akg==$0 M1WEB_MODULE_MAP12 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSbAJLgQwotC20Akg==$0 SYS_C0055476123456 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSaAJLgQwotC20Akg==$0 MOD_MODEL_INDEX123 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSZAJLgQwotC20Akg==$0 MOD_MAP_ID_INDEX12 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSVAJLgQwotC20Akg==$0 M1WEB_MODULE123456 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSUAJLgQwotC20Akg==$0 SYS_C0055483123456 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSPAJLgQwotC20Akg==$0 M1WEB_MODEL_TAGS12 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSOAJLgQwotC20Akg==$0 MOD_NAME_INDEX1234 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSLAJLgQwotC20Akg==$0 M1WEB_MOD_STORAGE3 TABLE 2013-03-08:12:10:55 YES

To recover table simply flashback table to before drop as:


SQL> flashback table M1WEB_MODULE_MAP12 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MODULE123456 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MODEL_TAGS12 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MOD_STORAGE3 to before drop;

Flashback complete.

Once, tables are back now we need to rename indexes and triggers to the original names. Mapping of recyclebin names to original name for tables and triggers is fine as above query has given the original names. So, we can rename indexes and triggers as:


SQL> alter index "BIN$12jgaeSlAJLgQwotC20Akg==$0" rename to PROJ_CREATED_INDEX;

Index altered.

SQL> alter index "BIN$12jgaeSkAJLgQwotC20Akg==$0" rename to PROJECT_NAME_INDEX;

Index altered.

SQL> alter index "BIN$12jgaeSbAJLgQwotC20Akg==$0" rename to SYS_C0055476;

Index altered.

Here is one interesting thing i noticed is that oracle generated nearly identicals names for idexes and tables with difference only in CAPS. Also no two objects of same type have identical names. Like, table – index have identical name not the table – table. Here is example from my first query.


SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
------------------------------ ------------------- ------ ------------------- ---
BIN$12jgaeSlAJLgQwotC20Akg==$0 PROJ_CREATED_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSLAJLgQwotC20Akg==$0 M1WEB_MOD_STORAGE3 TABLE 2013-03-08:12:10:55 YES

See the names are identical except 12th letter “l” is small in first name and in CAPS in second name. This was very confusing at first look but since we have original name with us, so no problem in rename of these objects.

Now, the real pain starts when it comes to rename constraints. Although, you can leave the constraint name in recyclebin format, but it’s not advisable. Now, the above query doesn’t contain the constraints, so we need to check the user_constraints and see the names of constraints which got renamed as well. Here is a small example, which is different from above one. In this example, i just wanted to show that how painful it is to rename a constraint.


SQL> create table yya_test (name varchar2(10) constraint upper_name check (name=upper(name)));

Table created.

SQL> create index yya_idx on yya_test(name);

Index created.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

no rows selected

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
UPPER_NAME

SQL> insert into yya_test values('yogesh');
insert into yya_test values('yogesh')
*
ERROR at line 1:
ORA-02290: check constraint (BACKUP.UPPER_NAME) violated

SQL> insert into yya_test values('YOGESH');

1 row created.

SQL> commit;

Commit complete.

Now, as soon as i drop the table, i got entries for table & index into recyclebin and constraint got renamed and original name is lost for constraint. I would love to raise this point to oracle (if i know where to raise), that constraint name should not be lost. Here are the line that shows that constraint name is lost:


SQL> drop table yya_test;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME DROPTIME CAN
------------------------------ -------------- ------- -------- ------------------- ---
BIN$1+WNXaW1AGTgQwotC20AZA==$0 YYA_IDX INDEX USERS 2013-03-14:17:34:02 NO
BIN$1+WNXaW2AGTgQwotC20AZA==$0 YYA_TEST TABLE USERS 2013-03-14:17:34:02 YES

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
BIN$1+WNXaW0AGTgQwotC20AZA==$0

I don’t know the solution for constraints name, if anyone does, please let me know too. Also, few restrictions on objects which can’t be restored from recyclebin. Here are lines from Oracle documentation:

When you drop a table, the table and all of its dependent objects go into the recycle bin together. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together. When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. Oracle Database retrieves all indexes defined on the table except for bitmap join indexes, and all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

Some dependent objects such as indexes may have been reclaimed because of space pressure. In such cases, the reclaimed dependent objects are not retrievable from the recycle bin.

Advertisements
Comments
  1. orasteps says:

    Well, i just asked the question about the lost constraint name on OTN discussion forums, original post can be found here . It seems that there is no solution for this and a enhancement needs to be raised with Oracle, but there is a very nice workaround suggested by “Tom Kyte” which can be found in link given on OTN discussion. I will give it a try and post the results and all thanks to Tom !!!!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s