How to restore the drop table in 19c Oracle Autonomous Data Warehouse.

 

In Oracle Autonomous Data Warehouse (ADW) 19c, if you have accidentally dropped a table and want to restore it, there are a few recovery options available depending on how the table was dropped and the time elapsed since then.

STEPS 1: Flashback - Using the Recycle Bin.

1. Check the Recycle Bin for the dropped table

SQL > SHOW RECYCLEBIN;

SQLselect * from DBA_RECYCLEBIN where ORIGINAL_NAME='TABLE_NAME';

2. Flashback the table (use the original table name)

SQL > FLASHBACK TABLE your_table_name TO BEFORE DROP;

SQL > FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

Flashback succeeded.

3. Verify the drop table data.

SQL > select * from TABLE_NAME;


Notes:

Recycle Bin is enabled by default in ADW.

You cannot use RMAN or traditional tablespace restore in ADW – it’s fully managed.

ADW automatically takes backups every 60 minutes and retains them for 60 days; however, point-in-time table-level recovery from these backups is not user-managed.

No comments:

How to add a datafile to a bigfile tablespace in Oracle 23ai Database.

  Syntax for Bigfile Tablespace: SQL > ALTER TABLESPACE tablespace_name ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED; Ho...