Flashback

Enable Flashback  Database

1- STARTUP MOUNT
2- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G
3- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='C:\FLASHBACK';
4- ARCHIVE LOG LIST; (convert to archive mode if in noarchivelog)
5- ALTER DATABASE FLASHBACK ON;
6- SELECT FLASHBACK ON FROM V$DATABASE;
7- ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2840;
8- ALTER SYSTEM SET RECYCLEBIN=ON;
9- ALTER DATABASE OPEN;

Flashback  Table




10- CONN SCOTT/TIGER
11- CREATE TABLE ID(ID NUMBER(4));
12- ALTER TABLE ID ENABLE ROW MOVEMENT;
13- SELECT CURRENT_SCN FROM V$DATABASE;(NOTE THE CURRENT_SCN NUMBER e.g. 123456)
14- INSERT INTO ID VALUES(1);
15- COMMIT;
16- SELECT CURRENT_SCN FROM V$DATABASE;(NOTE THE CURRENT_SCN NUMBER  e.g.7891234)
17- FLASHBACK TABLE ID TO SCN (GIVE SCN NUMBER e.g 123456)
18- SELECT COUNT(*) FROM ID;(YOU WILL SEE NO ROW SELECTED)
19- FLASHBACK TABLE TO SCN(GIVE LATEST SCN NUMBER e.g. 7891234)
20- SELECT COUNT(*) FROM ID;(YOU WILL SEE 1 ROW SELECTED)
21- DELETE FROM ID;
22- COMMIT;
23- FLASHBACK TABLE TO TIMESTAMP TO_TIMESTAMP('2010-25-01 10:54:00','YYYY-MM-DD HH24:MI:SS');
24- SELECT COUNT(*) FROM ID;
25- DROP TABLE ID;
26- DESC USER_RECYCLBIN;
27- SELECT OBJECT_NAME,TYPE FROM USER_RECYLEBIN;
28- FLASHBACK TABLE ID TO BEFORE DROP;
29- SELECT * FROM ID;






note : step#1 to step#9 not required for flashback table,
created by Syed Raheel