Difference between revisions of "Flashback"
From dbawiki
(Created page with "===Enable flashback=== exec dbms_flashback.enable; ===Disable flashback=== exec dbms_flashback.disable; ===To put the session back in time=== Note: undo_management must be auto...") |
|||
| Line 3: | Line 3: | ||
===Disable flashback=== | ===Disable flashback=== | ||
exec dbms_flashback.disable; | exec dbms_flashback.disable; | ||
| − | === | + | ===Put a session back in time=== |
Note: undo_management must be auto | Note: undo_management must be auto | ||
Note: Take a look at undo_retention to get an idea of how far back you might be able to go. | Note: Take a look at undo_retention to get an idea of how far back you might be able to go. | ||
exec dbms_flashback.enable_at_time(to_date('2012-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS')); | exec dbms_flashback.enable_at_time(to_date('2012-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS')); | ||
| + | ===Copy old data into a new table=== | ||
| + | *Create an empty copy of the source table | ||
| + | create table old_table_data | ||
| + | tablespace ts_data | ||
| + | as select * | ||
| + | from ruined_table | ||
| + | where rownum < 1; | ||
| + | *Insert the flashback data into it | ||
| + | insert into old_table_data | ||
| + | select * from ruined_table | ||
| + | as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' ); | ||
Revision as of 00:10, 11 November 2012
Contents
Enable flashback
exec dbms_flashback.enable;
Disable flashback
exec dbms_flashback.disable;
Put a session back in time
Note: undo_management must be auto Note: Take a look at undo_retention to get an idea of how far back you might be able to go.
exec dbms_flashback.enable_at_time(to_date('2012-AUG-24 12:00:00', 'YYYY-MON-DD HH24:MI:SS'));
Copy old data into a new table
- Create an empty copy of the source table
create table old_table_data tablespace ts_data as select * from ruined_table where rownum < 1;
- Insert the flashback data into it
insert into old_table_data select * from ruined_table as of timestamp to_timestamp ( '24-AUG-12 12:00:00', 'DD-MON-YY HH24:MI:SS' );
