Monday, December 5, 2016

How to perform flashback table forward and backward

CREATE TABLE TEST_FLASH(ID NUMBER);

ALTER TABLE TEST_FLASH ENABLE ROW MOVEMENT;

TRUNCATE TABLE TEST_FLASH;

INSERT INTO TEST_FLASH
SELECT LEVEL FROM DUAL CONNECT BY LEVEL<10000;

SELECT CURRENT_SCN FROM V$DATABASE;-- 63441120

INSERT INTO TEST_FLASH
SELECT LEVEL+10000 FROM DUAL CONNECT BY LEVEL<10000;

SELECT CURRENT_SCN FROM V$DATABASE;-- 63441184

SELECT COUNT(*) FROM TEST_FLASH; -- 19998

FLASHBACK TABLE TEST_FLASH to scn 63441120;

SELECT COUNT(*) FROM TEST_FLASH; -- 9999

FLASHBACK TABLE TEST_FLASH to scn 63441184;

SELECT COUNT(*) FROM TEST_FLASH; -- 19998

No comments:

Post a Comment