This was introduced in Oracle 9. It allows you to query the database as it was at some recent point in the past.
You enable Flashback at the session level and use a system time or a system change number (SCN) to tell Oracle how far back to go. Then you will be able to see data which has been committed at that point.
This feature uses the Automatic Undo Management functionality. The undo_retention initialisation parameter tells Oracle how many seconds to keep undo information for. It therefore determines how far back you can go.
I produced the example below on an Oracle 10 database running on Linux. First check that undo_management is set to auto, that you have an undo tablespace and that the undo_retention parameter is large enough:
SQL> select name, value
2 from v$parameter
3 where name like '%undo%'
4 /
NAME VALUE
-------------------- --------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
SQL>
Then create a test user and give it permission to use dbms_flashback:
SQL> create user flash identified by gordon
2 default tablespace users
3 quota unlimited on users
4 temporary tablespace temp
5 /
User created.
SQL> grant create session,
2 create table to flash
3 /
Grant succeeded.
SQL> grant execute on dbms_lock to flash
2 /
Grant succeeded.
SQL> grant execute on dbms_flashback to flash
2 /
Grant succeeded.
SQL>
Create a table called time_now containing 1 row. This will be updated regularly with the current time:
SQL> conn flash/gordon
Connected.
SQL> create table time_now as
2 select sysdate current_time from dual
3 /
Table created.
SQL>
Also create a table called control_times. Whenever time_now is updated with sysdate, that sysdate will be inserted into control_times:
SQL> create table control_times
2 (control_time date)
3 /
Table created.
SQL>
Update time_now and insert into control_times as described above 20 times with a second between each iteration. As you do this, display the values updated and inserted:
SQL> declare
2 display_time date;
3 begin
4 for a in 1..20 loop
5 update time_now
6 set current_time = (select sysdate from dual);
7 commit;
8 insert into control_times values (sysdate);
9 commit;
10 select current_time
11 into display_time
12 from time_now;
13 dbms_output.put_line('Current Time: '||
14 to_char(display_time,'hh24:mi:ss'));
15 select max(control_time)
16 into display_time from control_times;
17 dbms_output.put_line('Control Time: '||
18 to_char(display_time,'hh24:mi:ss'));
19 dbms_output.put_line('**********');
20 dbms_lock.sleep(1);
21 end loop;
22 end;
23 /
Current Time: 00:01:32
Control Time: 00:01:32
**********
Current Time: 00:01:33
Control Time: 00:01:33
**********
Current Time: 00:01:34
Control Time: 00:01:34
**********
Current Time: 00:01:35
Control Time: 00:01:35
**********
Current Time: 00:01:36
Control Time: 00:01:36
**********
Current Time: 00:01:37
Control Time: 00:01:37
**********
Current Time: 00:01:38
Control Time: 00:01:38
**********
Current Time: 00:01:39
Control Time: 00:01:39
**********
Current Time: 00:01:40
Control Time: 00:01:40
**********
Current Time: 00:01:41
Control Time: 00:01:41
**********
Current Time: 00:01:42
Control Time: 00:01:42
**********
Current Time: 00:01:43
Control Time: 00:01:43
**********
Current Time: 00:01:44
Control Time: 00:01:44
**********
Current Time: 00:01:45
Control Time: 00:01:45
**********
Current Time: 00:01:46
Control Time: 00:01:46
**********
Current Time: 00:01:47
Control Time: 00:01:47
**********
Current Time: 00:01:48
Control Time: 00:01:48
**********
Current Time: 00:01:49
Control Time: 00:01:49
**********
Current Time: 00:01:50
Control Time: 00:01:50
**********
Current Time: 00:01:51
Control Time: 00:01:51
**********
PL/SQL procedure successfully completed.
SQL>
Finally, read each time from the control_times table and try to flashback the database to that point. Then query the time from the time_now table. This should be equal to the value from the control_times table but there is often a slight difference. Apparently, if you flashback to an SCN, this difference disappears. I will try this out in a future post:
SQL> declare
2 display_time date;
3 cursor c1 is
4 select control_time from control_times
5 order by 1;
6 begin
7 for x in c1 loop
8 --
9 -- Try to flashback the database to each control_time:
10 --
11 dbms_flashback.enable_at_time(x.control_time);
12 dbms_output.put_line('Control Time: '||
13 to_char(x.control_time,'hh24:mi:ss'));
14 declare
15 unable_to_read_data exception;
16 pragma exception_init(unable_to_read_data,-01466);
17 consistent_read_failure exception;
18 pragma exception_init(consistent_read_failure,-08176);
19 begin
20 --
21 -- Now get the corresponding time from the TIME_NOW table.
22 -- It should be the same:
23 --
24 select current_time
25 into display_time from time_now;
26 dbms_output.put_line('Data Restored: '||
27 to_char(display_time,'hh24:mi:ss'));
28 dbms_output.put_line('**********');
29 dbms_flashback.disable;
30 exception
31 --
32 -- The flashback times are not always accurate
33 -- so you may not be able to read the table.
34 --
35 when unable_to_read_data then
36 dbms_output.put_line('Cannot read current_time from time_now');
37 dbms_output.put_line('**********');
38 dbms_flashback.disable;
39 when consistent_read_failure then
40 dbms_output.put_line('Consistent read failure');
41 dbms_output.put_line('**********');
42 dbms_flashback.disable;
43 end;
44 end loop;
45 end;
46 /
Control Time: 00:01:32
Cannot read current_time from time_now
**********
Control Time: 00:01:33
Cannot read current_time from time_now
**********
Control Time: 00:01:34
Data Restored: 00:01:34
**********
Control Time: 00:01:35
Data Restored: 00:01:34
**********
Control Time: 00:01:36
Data Restored: 00:01:34
**********
Control Time: 00:01:37
Data Restored: 00:01:37
**********
Control Time: 00:01:38
Data Restored: 00:01:37
**********
Control Time: 00:01:39
Data Restored: 00:01:37
**********
Control Time: 00:01:40
Data Restored: 00:01:40
**********
Control Time: 00:01:41
Data Restored: 00:01:40
**********
Control Time: 00:01:42
Data Restored: 00:01:40
**********
Control Time: 00:01:43
Data Restored: 00:01:43
**********
Control Time: 00:01:44
Data Restored: 00:01:43
**********
Control Time: 00:01:45
Data Restored: 00:01:43
**********
Control Time: 00:01:46
Data Restored: 00:01:46
**********
Control Time: 00:01:47
Data Restored: 00:01:46
**********
Control Time: 00:01:48
Data Restored: 00:01:46
**********
Control Time: 00:01:49
Data Restored: 00:01:49
**********
Control Time: 00:01:50
Data Restored: 00:01:49
**********
Control Time: 00:01:51
Data Restored: 00:01:49
**********
PL/SQL procedure successfully completed.
SQL>
