Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.
Example: Flashback the table back to previous time using SCN
Example: Flashback the table back to previous time using SCN
SQL> select count(*) from scott.test;Enable row movement:
COUNT(*)
----------
68781
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1584494
SQL> delete from scott.test where rownum <= 50000; 50000 rows deleted. SQL> commit;
Commit complete.
SQL> select count(*) from scott.test;
COUNT(*)
----------
18781
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1587106
SQL> alter table scott.test enable row movement;We can rewind the table back to previous time using timestamp:
Table altered.
SQL> FLASHBACK TABLE scott.test to scn 1584494;
Flashback complete.
SQL> select count(*) from scott.test;
COUNT(*)
----------
68781
SQL> alter table scott.test disable row movement;
Table altered.
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';Nice feature...
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009/08/30 17:01:09
SQL> delete from scott.test ;
68781 rows deleted.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009/08/30 17:03:18
SQL> select count(*) from scott.test;
COUNT(*)
----------
0
SQL> alter table scott.test enable row movement;
Table altered.
SQL> flashback table scott.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from scott.test;
COUNT(*)
----------
68781
(Thank You Bradd Piontek commend, After check 10gR2, we can rewind the table back to previous time as well)
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
SQL> create table test tablespace users as select * from all_objects;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
42981
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
---------------------
8361904200781
SQL> delete from test ;
42981 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
---------------------
8361904200441
SQL> alter table test enable row movement;
Table altered.
SQL> FLASHBACK TABLE test to scn 8361904200781;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
42981
Posts
4 comments:
It is possible I'm missing a subtle different between 11g and 10gR2, but I am pretty sure you can do this in 10gR2. In fact, I wrote a demo of this back in 2007.
No problem. I did a rather lengthy Demo of Flashback features in 10g at my last job. Never did turn it into a blog. It covers:
Flashback Transaction Query
Flashback Query (Statement)
Flashback Query (session)
Flashback Query (versions)
Flashback Drop
Flashback Table
I didn't go into Flashback Database as it is a bit different than the other flashback features, imho.
I know I have a Visio I put together somewhere that shows what versions each one works in and what oracle parameter is required for each one to work.
Thank You so much. I missed it...
and I tested It on 10gR2 as well.
It's work.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
SQL> create table test tablespace users as select * from all_objects;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
42981
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
---------------------
8361904200781
SQL> delete from test ;
42981 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
---------------------
8361904200441
SQL> alter table test enable row movement;
Table altered.
SQL> FLASHBACK TABLE test to scn 8361904200781;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
42981
Thank You again ;)
To Bradd Piontek,
Great to see your commendation. Actually I hope I see new article on your blog;)
About Flashback (Production).. I have a chance use Flashback Drop only ;)
Good... no problem on my database. So, I don't have a chance use other (flashback).
And we enabled flashback database, But We use nologging on some tablespaces, So we disabled it.
Thank You again. Helpful me to back read 10gR2 Docs again ;)
Post a Comment