Undrop table in Oracle

I don't "undrop" table a lot. So once when I tried to "undrop" a table, I got an error:

sql> undrop table test1;
SP2-0734:unknown command beginning "undrop tab..." - rest of line ignored.

Well, I soon found, it's not "undrop", instead:

SQL> flashback table test1 to before drop rename to test2;
Flashback complete.

You can rename it if the previous name has been used by others.

Oracle Flashback Query Using AS OF TIMESTAMP

Since Oracle 9i Release 2, you can use "AS OF SCN | TIMESTAMP" flashback query to query the "before" data of a changed table. It's useful to find the changes you made or recover errors.
I used TIMESTAMP more often and found it simple but efficient. I'll show some examples below.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

SQL> update employees set salary = salary * 1.05 where employee_id = 100;
1 row updated.

SQL> commit;

SQL> select salary from employees where employee_id = 100;
SALARY
----------
25200

Here shows the way to query the "before" data of yesterday:

SQL> select salary from employees AS OF TIMESTAMP sysdate - 1 where employee_id = 100;
SALARY
----------
24000

To show ten (10) minutes ago:

select salary from employees AS OF TIMESTAMP sysdate - 10/60/24 where employee_id = 100;

Or you can use function SYSTIMESTAMP instead of sysdate.

select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) where employee_id = 100;
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) where employee_id = 100;

To recover the change:

SQL>update employees e1 set salary =
( select salary from employees AS OF TIMESTAMP sysdate - 1 e2
where e2.employee_id = e1.employee_id )
where employee_id = 100;
1 row updated.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

You may sometimes want to save the before data to a temporary table, then compare and reccover.

create table tmp_employees nologging as select * from employees AS OF TIMESTAMP sysdate - 1;

The SELECT ... AS OF will not work after the table structure changes or table truncation.

SQL> truncate table tmp_objects;
Table truncated.

SQL> select count(*) from tmp_objects as of timestamp sysdate - 1/24/60;

ORA-01466: unable to read data - table definition has changed