Last active
December 1, 2018 18:15
-
-
Save nikola-bodrozic/1e01aae228828b739202ea173ba03bd0 to your computer and use it in GitHub Desktop.
Oracle Flashback
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- as admin | |
| CREATE TABLESPACE fda_ts DATAFILE 'fda_ts_tabspace' SIZE 10M AUTOEXTEND ON NEXT 1M; | |
| ALTER USER scott QUOTA UNLIMITED ON fda_ts; | |
| CREATE FLASHBACK ARCHIVE fda_1month TABLESPACE fda_ts QUOTA 15M RETENTION 1 MONTH; | |
| GRANT FLASHBACK ARCHIVE ON fda_1month TO scott; | |
| GRANT FLASHBACK ARCHIVE ADMINISTER TO scott; | |
| GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO scott; | |
| GRANT CREATE ANY CONTEXT TO scott; | |
| EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL'); | |
| -- Check point | |
| SELECT owner_name, | |
| flashback_archive_name, | |
| flashback_archive#, | |
| retention_in_days, | |
| TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time, | |
| TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time, | |
| status | |
| FROM dba_flashback_archive | |
| ORDER BY owner_name, flashback_archive_name; | |
| -- as scott | |
| CREATE TABLE tab1 ( | |
| id NUMBER, | |
| description VARCHAR2(50), | |
| CONSTRAINT tab_1_pk PRIMARY KEY (id) | |
| ) FLASHBACK ARCHIVE fda_1month; | |
| -- checkpoint | |
| SELECT owner_name, | |
| table_name, | |
| flashback_archive_name, | |
| archive_table_name, | |
| status | |
| FROM dba_flashback_archive_tables | |
| ORDER BY owner_name, table_name; | |
| INSERT INTO tab1 VALUES (1, 'ONE'); | |
| COMMIT; | |
| UPDATE tab1 SET description = 'TWO' WHERE id = 1; | |
| COMMIT; | |
| UPDATE tab1 SET description = 'THREE' WHERE id = 1; | |
| COMMIT; | |
| -- checkpoint | |
| SELECT versions_startscn, | |
| --versions_starttime, | |
| versions_endscn, | |
| --versions_endtime, | |
| versions_xid, | |
| versions_operation, | |
| description, | |
| DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user, | |
| DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier, | |
| DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute | |
| FROM tab1 | |
| VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24) AND SYSTIMESTAMP | |
| WHERE id = 1 | |
| ORDER BY versions_startscn; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment