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.Download my script here: http://halisway.hifichoice.com/flashback_user.sql.
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.
Read more about flashback here.
7 comments:
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;
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;
/
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!
Please advise how I can download a copy of your flashuser.sql script
Thank You
Ravin Maharj
ravin.maharaj@multichoice.co.za
hello you could facilitate the your scripts flashuser.sql
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 ?
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.
Post a Comment