Informationen aus der Vergangenheit… (ab Oracle 9i)

Benutzerrechte einrichten

CONNECT / AS SYSDBA
ALTER SYSTEM SET UNDO_RETENTION = 3600;
SHOW PARAMETER UNDO_RETENTION;
GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
GRANT FLASHBACK ANY TABLE TO SCOTT;

User wechseln

CONNECT SCOTT/TIGER

Tabelle anlegen

CREATE TABLE emp_test_flash AS SELECT * FROM emp;
5 Minuten Pause !!!

SCN-Nummer ermitteln

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
27903086

testen…

SELECT COUNT(*) FROM emp_test_flash;
DELETE FROM emp_test_flash;
COMMIT;
SELECT COUNT(*) FROM emp_test_flash;

(ab Oracle 9.0.x)
SCN-Nummer übernehmen…und Flashbacksitzung starten

EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (27903086);
SELECT COUNT(*) FROM emp_test_flash;

Flashbacksitzung beenden…

EXECUTE DBMS_FLASHBACK.DISABLE

(ab Oracle 9.2.x)
SCN-Nummer übernehmen…und Flashbacksitzung starten… und wieder beenden

SELECT * FROM emp_test_flash AS OF SCN 27903086;