Flashback a user or schema in Oracle

Since 10g we've had the quite cool feature 'flashback', or rather we've had it since waay back. But now Oracle gave us an easy way to use it. We have the recyclebin as a new feature though. Cool but sometimes a bit confusing.

Flashback can be done at two levels, the whole database or for a single object. Why on earth didn't Oracle include a "flashback schema" feature. I would guess most users use flashback when doing testing, like schema upgrades and things like that. And if the test fails you want to restore the tables to before the scripts ran (ok workspaces comes to mind here but that's another post).
I took some time and wrote a script to flashback all tables and dependant objects from the recyclebin and to flashback existing tables to a previous version.
Since flashback require row movement to be enabled the script checks if it needs to enable that for the tables and will do so if needed.

-- Ok, let's create a little mess we can clear up.
SQL> drop table i;

Table dropped.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

SQL> update a set d=sysdate;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 21-JAN-07 11.29.53.000000 PM
         2 21-JAN-07 11.29.53.000000 PM

-- Commited and all! We sure screwed that up

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
T                              TABLE
D                              TABLE
BIN$J5ZNZK2dxdvgQKjAKF9ZFQ==$0 TABLE

4 rows selected.

-- Lets run the script to generate our flashback script
-- The script will prompt you for the number of minutes you want to go back
SQL> @flashback_user
How far back do you want to flashback (in minutes)?
Enter value for minute: 8
         8
testuser

Spooling flashback_user_testuser.sql

alter table T enable row movement;
flashback table A to timestamp sysdate - interval '8' minute;
flashback table T to timestamp sysdate - interval '8' minute;
flashback table D to timestamp sysdate - interval '8' minute;
flashback table I to before drop;

SQL> @flashback_user_testuser

Table altered.


Flashback complete.


Flashback complete.


Flashback complete.


Flashback complete.

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
D                              TABLE
T                              TABLE
I                              TABLE

4 rows selected.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

2 rows selected.
-- And we are back in business.

Download my script here: http://halisway.hifichoice.com/flashback_user.sql.

Read more about flashback here.