How to rescue data after a mistake

Oracle give you the opportunity to recover data also if you delete them or dropped the table.

Here 2 simple example very useful in a panic moment.

Drop table

1) Create the table

CREATE TABLE TEST01 (
        first_name VARCHAR2(10),
        last_name VARCHAR2(20));        

2) Check if the table exist

SELECT table_name FROM user_tables WHERE table_name = 'TEST01';        

3) Drop the table

DROP TABLE TEST01;        

4) Check if the table exist

SELECT table_name FROM user_tables WHERE table_name = 'TEST01';        

5) Restore the table

FLASHBACK TABLE TEST01 TO BEFORE DROP;        

6) Check if the table exist

SELECT table_name FROM user_tables WHERE table_name = 'TEST01';        

6.1) Optionally, you can rename the table

FLASHBACK TABLE TEST01 TO BEFORE DROP RENAME TO test01_restored;        

6.2) Check if the table exist. Here I check if both tables exists

SELECT table_name FROM user_tables WHERE table_name = 'TEST01'
UNION ALL
SELECT table_name FROM user_tables WHERE table_name = 'TEST01_RESTORED';        

Delete data

1) Create table

CREATE TABLE TEST01 (
    first_name VARCHAR2(10),
    last_name VARCHAR2(20));        

2) Insert data

INSERT ALL
    INTO TEST01 (first_name, last_name) VALUES ('Andrea', 'Salzano')
    INTO TEST01 (first_name, last_name) VALUES ('Diego', 'Maradona')
    INTO TEST01 (first_name, last_name) VALUES ('Francesco', 'Totti')
SELECT 1 FROM DUAL;
COMMIT;        

3) Show the records

SELECT * FROM TEST01;        

4) Delete records

DELETE FROM TEST01;
COMMIT;        

5) Show the records

6) Restore data

SELECT *
FROM TEST01 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);        

You save the results in a temporary table and then choose the best way to proceed

CREATE TABLE TEST01_RES
AS
SELECT *
FROM TEST01
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);        

NOTE.

• The SQL*Plus column separator is configured, with SET COLSEP "|"
• Flashback table from DZone
• Flashback table from OracleBase
• Flashback 1 query from OracleBase
• Flashback 2 query from OracleBase
• Fllashback from complexsql