Pages

Saturday, September 5, 2009

FlashBack Example


Monetize your site!


Enabling FlashBack


Startup mount / startup mount exclusive

alter system set db_recovery_file_dest = ‘path’;

alter system set db_recovery_file_dest_size = 4g; -- (Default 2G)
alter database archivelog; -- (If DB is not in archive mode)
alter database flashback on;
select flashback_on from V$database;
alter system set db_flashback_retention_target=2840 -- (in minutes)
alter system set recyclebin=on;
alter database open;
conn scott/tiger
create table orasoft (id number(5));
alter table orasoft enable row movement; -- (to rewind the table we use row movement)
select current_scn from V$database; -- (from sys user)
insert into orasoft values(1);
commit;
select current_scn from V$database; -- (from other session (sys user))
flashback table orasoft to scn 123456; -- (number obtain from v$database.current_scn)
select count(*) from orasoft;

delete orasoft; -- (note the sysdate)
select count(*) from orasoft; -- (will return 0 rows)
Flashback table orasoft to timestamp to_stamp(‘2009-09-03 09-55-00’, ‘RRRR-MM-DD
HH24-MI-SS’);

SELECT COUNT(*) FROM ORASOFT;
To recover a Dropped Table

DROP TABLE ORASOFT;
FLASHBACK TABLE ORASOFT TO BEFORE DROP;
SELECT*FROM ORASOFT;
To recover & rename a Dropped Table
DROP TABLE ORASOFT;

FLASHBACK TABLE ORASOFT TO BEFORE DROP rename to new_name;
SELECT*FROM new_name;




Monetize your site!