Sunday, August 30, 2009

Rewinding a Table Using Oracle Flashback Table

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

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
SQL> select count(*) from scott.test;

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
Enable row movement:
SQL> alter table scott.test enable row movement;

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.
We can rewind the table back to previous time using timestamp:
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

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
Nice feature...

(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

4 comments:

Bradd Piontek said...

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.

Bradd Piontek said...

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.

Surachart Opun said...

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 ;)

Surachart Opun said...

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 ;)