6 Replies Latest reply: Jul 31, 2012 9:05 AM by 767685 RSS

    restore deleted records from a table

    767685
      Hi,

      Somebody deleted records from 10,12 tables in one of the schema. I found in one of the forum that table can be restored also if it exists in the recyclbin. I checked by querying:
      select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
      
       > select type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin WHERE TYPE='TABLE' and droptime like '%2012-07-31%';
      PHONE      TABLE                 YES YES 2012-07-31:10:23:08
      TABLE                 YES YES 2012-07-31:10:23:08
      TABLE                 YES YES 2012-07-31:10:23:08
      TABLE                 YES YES 2012-07-31:10:23:08
      TABLE                 YES YES 2012-07-31:10:23:08
      - - 
      - - 
      I created a test table and dropped it and restored from recyclebin using following flashback table test query....but in my case tables are not dropped...these tables are shoing 0 records. How can I recover only records from recyclbin tables?
      SQL> flashback table test to before drop;

      Flashback complete.

      DB=10.2.0.5 on RHL

      Thanks a lot.

      Best Regards
        • 1. Re: restore deleted records from a table
          anand prakash - oracle
          Hi,

          Depending on when the data was deleted and the undo setting, you can use flashback query, which would read data from undo if avaliable.Also you can use flashback table functionality.But again all of it is dependent on undo data availability.In case its not, then you need to perform point in time recovery of the database from backup (can be done on some test env) and then export/import the data.

          http://www.oracle-base.com/articles/10g/flashback-10g.php

          http://www.oracle-developer.net/display.php?id=320

          Anand

          Edited by: Anand.. on Jul 31, 2012 4:16 PM
          • 2. Re: restore deleted records from a table
            767685
            Hi,

            Thank you.

            This is the PHONE table from and I counted rows its zero in the table now.
            SQL> select count(*) from "BIN$xhzjX2un3EjgQBSseT8u0Q==$0";
            
              COUNT(*)
            ----------
                353363
            
            but when I do
            
            SQL> flashback table PHONE to before drop;
            flashback table PHONE to before drop
            *
            ERROR at line 1:
            ORA-38312: original name is used by an existing object
            
            when I try using insert: it voiles Foreign key 
            SQL> insert into PHONE
              2  select * from "BIN$xhzjX2un3EjgQBSseT8u0Q==$0";
            insert into PHONE
            *
            ERROR at line 1:
            ORA-02291: integrity constraint (AD.FK4BB9939C4) violated - parent key not found
            I disabled the table constraint and then above insert from bin worked but when I enable it again says:
            alter table phone enable constraint FK1D0C220D4
            alter table phone enable constraint FK1D0C220D4
            *
            ERROR at line 1:
            ORA-02298: cannot validate (AD.FK1D0C220D4) - parent keys not found

            Regards,
            • 3. Re: restore deleted records from a table
              Girish Sharma
              See this demo on my test 11.2.0.1 on Windows :
              SQL> create table phone(ph_number number);
              
              Table created.
              
              SQL> insert into phone values(123456);
              
              1 row created.
              
              SQL> insert into phone values(11111);
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> drop table phone;
              
              Table dropped.
              
              SQL> create table phone(phno number);
              
              Table created.
              
              SQL> insert into phone values (2222);
              
              1 row created.
              
              SQL> insert into phone values (33333);
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> flashback table PHONE to before drop;
              flashback table PHONE to before drop
              *
              ERROR at line 1:
              ORA-38312: original name is used by an existing object
              
              
              SQL> alter table phone rename to ph;
              
              Table altered.
              
              SQL> flashback table PHONE to before drop;
              
              Flashback complete.
              
              SQL> select * from phone;
              
               PH_NUMBER
              ----------
                  123456
                   11111
              
              SQL> select * from ph;
              
                    PHNO
              ----------
                    2222
                   33333
              
              SQL>
              So, just rename the current PHONE table to a new name; something like TMP_PHONE and then :

              flashback table PHONE to before drop;

              It should work for you too.

              Regards
              Girish Sharma
              • 4. Re: restore deleted records from a table
                767685
                Thanks a lot.

                I renamed phone table ph and flashback before drop, but I see the following difference through table records are recovered, i.e. Constraint names are changed or what and also Foreign key is missing i think. Can it be resolved?
                SQL> select constraint_name from user_constraintS where table_name='PHONE';
                
                CONSTRAINT_NAME
                ------------------------------
                BIN$xhzjX2uZ3EjgQBSseT8u0Q==$0
                BIN$xhzjX2ua3EjgQBSseT8u0Q==$0
                BIN$xhzjX2ub3EjgQBSseT8u0Q==$0
                
                SQL> select constraint_name from user_constraintS where table_name='PH';
                
                CONSTRAINT_NAME
                ------------------------------
                SYS_C00126762
                SYS_C00126763
                SYS_C00126764
                FK1D0C220D41
                Thanks
                Best Regards
                • 5. Re: restore deleted records from a table
                  anand prakash - oracle
                  Hi,

                  If you have select * from user_recylebin ouput , you can see the ORIGINAL_NAME from it and rename the constraints using "alter table phone rename constraint "BIN$ceFgFlCpS8uiTo+xtfT2oA==$0" to "SYS_C00XXXXX"; " command, so some other name.
                  17:34:07 SQL> select * from user_recyclebin;
                  
                  OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN
                  ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ----------
                  PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
                  -------------------------------- --- --- ---------- ----------- ------------ ----------
                  BIN$1NMjc8flR764Q4qbnRdgPg==$0 SYS_C0013690                     DROP      INDEX                     USERS                          2012-07-31:17:27:58 2012-07-31:17:34:07    5338944
                                                   NO  YES      81312       81312        81313          8
                  
                  BIN$bXzukO/1R8Kp7lhDDLq9PQ==$0 PHONE                            DROP      TABLE                     USERS                          2012-07-31:17:27:04 2012-07-31:17:34:07    5338952
                                                   YES YES      81312       81312        81312          8
                  
                  17:34:13 SQL> flashback table PHONE to before drop;
                  
                  Flashback complete.
                  
                  17:34:19 SQL>
                  17:34:19 SQL>
                  17:34:19 SQL> @constraints
                  Enter value for owner: anand
                  Enter value for table_name: PHONE
                  
                  OWNER                CONSTRAINT_NAME                CONST INDEX_NAME        R_OWNER                     R_CONSTRAI STATUS
                  -------------------- ------------------------------ ----- ----------------- ------------------------------ ---------- --------
                  ANAND                BIN$iUBXbvenSqq+BklOlLi97A==$0 P     BIN$1NMjc8flR764Q                                   ENABLED
                                                                            4qbnRdgPg==$0
                  
                  
                  Enter value for owner: anand
                  Enter value for table_name: phone
                  
                  OWNER                TABLE_NAME                     COLUMN_NAME                    CONSTRAINT_NAME          POSITION
                  -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
                  ANAND                PHONE                          PH_NUMBER                      BIN$iUBXbvenSqq+BklOlLi97A==$0          1
                  
                  17:34:36 SQL> @index
                  Enter value for owner: anand
                  Enter value for table_name: phone
                  
                  TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME        TABLESPACE_NAME        NUM_ROWS      CLUST STATUS   INDEX_TYPE
                  -------------------- ------------------------------ -------------------- ----------------- -------------------- ---------- ---------- -------- ------------
                  ANAND                PHONE                          ANAND                BIN$1NMjc8flR764Q USERS                 0          0 VALID    NORMAL
                                                                                           4qbnRdgPg==$0
                  
                  
                  17:34:45 SQL>
                  17:34:46 SQL>
                  17:34:46 SQL> alter table phone rename constraint "BIN$iUBXbvenSqq+BklOlLi97A==$0" to  "SYS_C00XXXXX";
                  
                  Table altered.
                  
                  17:35:05 SQL> @constraints
                  Enter value for owner: anand
                  Enter value for table_name: PHONE
                  
                  OWNER                CONSTRAINT_NAME                CONST INDEX_NAME        R_OWNER                     R_CONSTRAI STATUS
                  -------------------- ------------------------------ ----- ----------------- ------------------------------ ---------- --------
                  ANAND                SYS_C00XXXXX                   P     BIN$1NMjc8flR764Q                                   ENABLED
                                                                            4qbnRdgPg==$0
                  
                  
                  Enter value for owner: anand
                  Enter value for table_name: phone
                  
                  OWNER                TABLE_NAME                     COLUMN_NAME                    CONSTRAINT_NAME          POSITION
                  -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
                  ANAND                PHONE                          PH_NUMBER                      SYS_C00XXXXX                    1
                  
                  17:35:12 SQL> alter index "BIN$1NMjc8flR764Q4qbnRdgPg==$0" rename to "PHONE_PK";
                  
                  Index altered.
                  
                  17:35:45 SQL> @index
                  Enter value for owner: anand
                  Enter value for table_name: phone
                  
                  TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME        TABLESPACE_NAME        NUM_ROWS      CLUST STATUS   INDEX_TYPE
                  -------------------- ------------------------------ -------------------- ----------------- -------------------- ---------- ---------- -------- ------------
                  ANAND                PHONE                          ANAND                PHONE_PK          USERS                 0          0 VALID    NORMAL
                  
                  17:35:54 SQL>
                  HTH
                  Anand
                  • 6. Re: restore deleted records from a table
                    767685
                    Thank you ver much Anand and Grish.

                    Appreciate your effort.

                    At the moment I renamed table to abc_old kind of and then flashback table abc to before drop;

                    records are recovered. I will see the constraints rename thing later.

                    Best Regards