Table restoring oracle10g

Question

So I have really awkward topic in here. One table on oracle server 10g was dropped and recreated and it shouldn't be. Structure of table was changed and now I'm not able to flash it back now. Dump is from previous day but I'm not really sure how to recover just one table. Any ideas how to proceed this topic?

Thanks a lot. Peter

Is this a commercial database? If so can't you restore from tape and use transaction logs to roll forward to a point just before the table was deleted? — w0051977, Oct 13 '12 at 10:26
Yes, it's commerecial db. And even I'm not db admin, so in fact I have no idea what's are possibilities to restore it. — user1275513, Oct 13 '12 at 10:41
The question is too vague. Is the database backed up? e.g. using Net Backup? — w0051977, Oct 13 '12 at 10:49
Database is somehow backuped. I'm not sure how exactly, however we have dmp files which we should be able to use. Is there any possibility to use dbms_datapump? — user1275513, Oct 13 '12 at 10:51
possible duplicate of [Import specific tables from oracle dump file?](http://stackoverflow.com/questions/11824758/import-specific-tables-from-oracle-dump-file) — Jon Heller, Oct 13 '12 at 18:08

Nick Krasnov · Answer 1 · 2012-10-14T16:36:22.530

To restore the table that has been dropped(you either should restore it with a different name or drop the table that has the same name as the table that is being restored) you can use either imp utility (for example) and that dump file you have by specifying a table name as the value of tables parameter (tables=(your_table_name)) (In this case you either have to drop already existed with the same name table in your schema or import the table into a different schema.), or you can restore the table by using flashback statement if recycle bin option is enabled. Here is an example of restoring a table using flashback statement:

SQL> drop table tb_test;

Table dropped

SQL> drop table tb_test1;

Table dropped

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> create table tb_test(id number);

Table created

SQL> insert into tb_test(id) values(123);

1 row inserted

SQL> commit;

Commit complete

SQL> select object_name, original_name, operation from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION 
------------------------------------------------------------------------------
BIN$sCyDUbMsQ9qOotUQZUTt9g==$0 TB_TEST1                         DROP
BIN$uVD0HKOsQv26JTT3Q0PuOg==$0 TB_TEST                          DROP      

SQL> purge recyclebin;

Done

SQL> select object_name, original_name, operation  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION 
------------------------------------------------------------------------------

SQL> drop table tb_test;

Table dropped

SQL> select object_name, original_name, operation from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION 
------------------------------------------------------------------------------
BIN$+shfpD3rQKy/ry5LKtkJaw==$0 TB_TEST                          DROP      

SQL> create table tb_test(id number, col1 varchar2(11));

Table created

SQL> insert into tb_test(id, col1) values(11, 'string');

1 row inserted

SQL> commit;

Commit complete

SQL> flashback table tb_test to before drop;

flashback table tb_test to before drop

ORA-38312: original name is used by an existing object

SQL> flashback table tb_test to before drop rename to tb_test1;

Done

SQL> select * from tb_test1;

        ID
----------
       123

SQL> select * from tb_test;

        ID COL1
---------- -----------
        11 string

SQL>

Table restoring oracle10g

1 Answers1