ORACLE – DBMS_FLASHBACK

ORACLE – DBMS_FLASHBACK

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
SQL > 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;

TIPP:

Über die Seminare informieren