Sunday, January 21

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.

7 comments:

Anonymous said...

Hi , i have read your blog as above .
Meanwhile, i have a little confusion as belows:
if i "Drop User Smith", do u think it can be flashed back using flashback technology.
115. Which two operations can be flashed back using the Flashback technology? (Choose two.)
A) DROP USER SMITH;
B) DROP TABLE EMPLOYEES;
C) DROP TABLESPACE USERS;
D) ALTER TABLE SALES_REP DROP PARTITION P1;E) ALTER TABLE EMPLOYEES DROP COLUMN DESIG_ID;

rajpura said...

Script to Flashback table or few tables

SET SERVEROUTPUT ON SIZE 2000000
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('TABLE')
AND object_name IN upper('&1'))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" ENABLE ROW MOVEMENT';
EXECUTE IMMEDIATE 'FLASHBACK ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" to timestamp (SYSTIMESTAMP - INTERVAL ''20''
minute)';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('FAILED: FLASHBACK ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" to timestamp (SYSTIMESTAMP - INTE
RVAL ''20'' minute)');
END;
END LOOP;
END;
/

Pollocks said...

Great concept. I'm going to build upon this:

1) identify all tables where the contents have changed during the period of interest (changed if sum(dba_hist_seg_stats.db_block_changes_delta) > 0 for the given object)
2) alter those tables enable row movement if required.
3) flashback those tables

thanks!

maharajdba said...

Please advise how I can download a copy of your flashuser.sql script

Thank You
Ravin Maharj
ravin.maharaj@multichoice.co.za

Unknown said...

hello you could facilitate the your scripts flashuser.sql

Unknown said...

suppose in my oracle database there are 3 schemas- say - A, B and C. If want to run flashback only for schema-"B", then what need to be done ?

Michael said...

I speculate About the reason why Oracle did not introduce a "FLASHBACK SCHEMA" command. I think Oracle wants to force anybody to buy a multi-tenant-License. When you put each Schema (in case of duplicate tables in one DB) in a single PDB (plugged-in-database) the "FLASCHBACK DATABASE" command would work.