3 Replies Latest reply: Oct 9, 2012 3:23 PM by jgarry RSS

    Recovering dropped table without using pointing time recovery/

    xanthusblack
      Hi geeks,

      Noob here.I have a question*.I want to recover the dropped table with data with out using pointing time recovery.*

      for Ex:Use hasr dropped the table at 10:00 and realized that he dropped the table at 16:00hrs .So i want to fetch the data(DDL+DML) without using pointing time recovery.

      So please find answers for my question.

      Thank you,
      Rakesh M.
        • 1. Re: Recovering dropped table without using pointing time recovery/
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
          >
          Noob here.I have a question*.I want to recover the dropped table with data with out using pointing time recovery.*

          for Ex:Use hasr dropped the table at 10:00 and realized that he dropped the table at 16:00hrs .So i want to fetch the data(DDL+DML) without using pointing time recovery.
          >
          FLASHBACK TABLE myTable TO BEFORE DROP;

          See the examples in the FLASHBACK TABLE section of the SQL Language doc
          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm
          >
          Retrieving a Dropped Table: Example If you accidentally drop the pm.print_media table and want to retrieve it, then issue the following statement:

          FLASHBACK TABLE print_media TO BEFORE DROP;

          If another print_media table has been created in the pm schema, then use the RENAME TO clause to rename the retrieved table:

          FLASHBACK TABLE print_media TO BEFORE DROP RENAME TO print_media_old;

          If you know that the employees table has been dropped multiple times, and you want to retrieve the oldest version, then query the USER_RECYLEBIN table to determine the system-generated name, and then use that name in the FLASHBACK TABLE statement. (System-generated names in your database will differ from those shown here.)

          SELECT object_name, droptime FROM user_recyclebin
          WHERE original_name = 'PRINT_MEDIA';

          OBJECT_NAME DROPTIME
          ------------------------------ -------------------
          RB$$45703$TABLE$0 2003-06-03:15:26:39
          RB$$45704$TABLE$0 2003-06-12:12:27:27
          RB$$45705$TABLE$0 2003-07-08:09:28:01
          • 2. Re: Recovering dropped table without using pointing time recovery/
            xanthusblack
            Thank you sir.But without flashback can I do it and without pointing time ??? Is it possible or not??
            • 3. Re: Recovering dropped table without using pointing time recovery/
              jgarry
              You can [url http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN11681]query objects in the recycle bin if you have a recycle bin. You can get the information from a previous export, up until the export was made. You can used a delayed standby if you have it set up.

              Edit: You can mine archived logs if you have all since the object was created.

              What is your situation and what are you trying to accomplish?

              Edited by: jgarry on Oct 9, 2012 1:23 PM