How to recover Dropped TABLE FROM FLASH BACK in Oracle

How to recover Dropped  TABLE  FROM FLASH BACK - Oracle

    In Oracle, you can recover a dropped table using the Flashback Drop feature if it's enabled. Flashback Drop allows you to recover dropped tables or other objects without having to perform a full database restore. However, this feature is only available if you've enabled and configured the Flashback technology in your Oracle database.

Here are the general steps to recover a dropped table using Flashback Drop:

1. Check Flashback Feature: Ensure that the Flashback feature is enabled in your Oracle database. You can verify this by querying the DBA_FLASHBACK_DATABASE view or by checking the database initialization parameters.

2. 
   

3. Identify the dropped table: You need to know the name of the dropped table and when it was dropped. This information will be useful for the flashback operation.

4. 
   

5. Determine the retention period: Flashback Drop operates within a specified retention period.

6. 
   

7. If the table was dropped within this retention period, you can recover it. You can check the value of the FLASHBACK_RETENTION_TARGET parameter to see how far back in time you can recover.

8. 
   

9. Perform Flashback Operation:

10. 
    

• Use the FLASHBACK TABLE command to recover the dropped table.

• 
  

• Here's an example:

11. 
    
12. Create table RP_TABLE5(N NUMBER); INSERT INTO RP_TABLE5 VALUES(1234); INSERT INTO RP_TABLE5 VALUES(3456); COMMIT; SELECT * FROM RP_TABLE5; DROP TABLE RP_TABLE5; -- dropping table SELECT * FROM RP_TABLE5; FLASHBACK TABLE RP_TABLE5 TO BEFORE DROP;
13. -- Dropped table is recovered
14. or
16. FLASHBACK TABLE RP_TABLE5 TO BEFORE DROP RENAME TO RECOVERED_TABLE;
17. -- Dropped table is recovered with new name RECOVERED_TABLE
19. 
    
20. 
    
Verify: After executing the flashback operation, verify that the dropped table has been recovered successfully.
21. SELECT * FROM RP_TABLE5;
22. or
23. SELECT * FROM RECOVERED_TABLE;

NOTE:- Flashback Drop has limitations, and it may not always be possible to recover dropped objects depending on various factors such as retention period settings, available space, and system activity. Therefore, it's recommended to regularly back up your database to ensure you have a reliable recovery mechanism.