This discussion is archived
3 Replies Latest reply: Oct 9, 2012 1:23 PM by jgarry RSS

Recovering dropped table without using pointing time recovery/

xanthusblack Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points