create user demo identified by demo default tablespace users;
grant create session, flashback any table, create table to demo;
grant execute on dbms_flashback to demo;
alter user demo quota unlimited on users;
SQL> connect / as sysdba
Connected.
SQL> show parameter undo
NAME TYPE VALUE
---------------------------- ----------- ---------------
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
SQL> select instance_name, archiver from v$instance;
INSTANCE_NAME ARCHIVE
---------------- -------
demo STARTED
SQL> conn demo/demo
Connected.
SQL> create table prez (name varchar2(25), elected varchar2(4));
Table created.
SQL> insert into prez values ('Barack Obama', '2008');
1 row created.
SQL> insert into prez values ('George W. Bush', '2000');
1 row created.
SQL> insert into prez values ('Bill Clinton', '1992');
1 row created.
SQL> insert into prez values ('George H. W. Bush', '1988');
1 row created.
SQL> insert into prez values ('Ronald Reagan', '1980');
1 row created.
SQL> select * from Prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2000
Bill Clinton 1992
George H. W. Bush 1988
Ronald Reagan 1980
SQL> commit;
Commit complete.
SQL> alter session set nls_date_Format = 'YYYY-MM-DD:HH24:MI:SS';
Session altered.
SQL> set serveroutput on
SQL> var scn number;
SQL> exec :scn := dbms_flashback.get_system_change_number;
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-20:22:49:06
SQL> update prez set elected = '2013';
5 rows updated.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-20:22:49:32
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2013
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> exec dbms_flashback.enable_at_system_change_number(:scn);
PL/SQL procedure successfully completed.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2000
Bill Clinton 1992
George H. W. Bush 1988
Ronald Reagan 1980
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2013
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> select * from prez as of scn :scn;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2000
Bill Clinton 1992
George H. W. Bush 1988
Ronald Reagan 1980
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2013
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> exec dbms_flashback.enable_at_time(to_date('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS'));
PL/SQL procedure successfully completed.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2000
Bill Clinton 1992
George H. W. Bush 1988
Ronald Reagan 1980
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
SQL> select * from prez
2 as of timestamp
3 to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS');
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2000
Bill Clinton 1992
George H. W. Bush 1988
Ronald Reagan 1980
SQL> select * from prez
2 as of timestamp
3 to_timestamp('2012-11-20:22:49:32','YYYY-MM-DD:HH24:MI:SS');
NAME ELEC
------------------------- ----
Barack Obama 2013
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> update prez set elected = '2008' where name = 'Barack Obama';
1 row updated.
SQL> select * from prez;
NAME ELEC
------------------------- ----
Barack Obama 2008
George W. Bush 2013
Bill Clinton 2013
George H. W. Bush 2013
Ronald Reagan 2013
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-20:23:11:54
SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801
PL/SQL procedure successfully completed.
SQL> var scn2 number;
SQL> exec :scn2 := dbms_flashback.get_system_change_number;
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn2));
SCN = 820456
PL/SQL procedure successfully completed.
SQL> select * from prez
2 versions between scn 818801 and 820456
3 where name = 'Barack Obama';
NAME ELEC
------------------------- ----
Barack Obama 2008
Barack Obama 2013
Barack Obama 2008
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-11-20:23:19:24
SQL> select * from prez
2 versions between timestamp
3 to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS')
4 and to_timestamp('2012-11-20:23:20:00','YYYY-MM-DD:HH24:MI:SS')
5 where name = 'Barack Obama';
NAME ELEC
------------------------- ----
Barack Obama 2008
Barack Obama 2013
Barack Obama 2008
create table temp_prez as select * from prez as of scn :scn;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)