Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Basic Functionality
Flashback Version Query
SELECT <columns>
FROM <schema_name.table_name>
VERSIONS BETWEEN SCN <minimum_scn> AND <maximum_scn>
[WHERE <column_filter>]
[GROUP BY <non-aggregated_columns>]
[HAVING <group filter>
[ORDER BY <position_numbers_or_column_names>]
conn uwclass/uwclass@pdbdev
CREATE TABLE t AS
SELECT owner, object_name, object_type
FROM all_objects
WHERE 1=2;
desc t
DECLARE
CURSOR fvq_cur IS
SELECT owner, object_name, object_type
FROM all_objects
WHERE rownum < 11;
BEGIN
FOR r IN fvq_cur LOOP
INSERT INTO t
VALUES r;
COMMIT; -- this is a bad practice done here intentionally
END LOOP; -- do not code incremental commits
END;
/
set linesize 141
col owner format a6
SELECT * FROM t;
SELECT versions_xid, versions_startscn,
versions_endscn, versions_operation, owner, object_name, object_type
FROM t
VERSIONS BETWEEN
SCN MINVALUE AND MAXVALUE;
UPDATE t
SET object_type = 'VIEW'
WHERE object_type <> 'VIEW'
AND rownum = 1;
COMMIT;
UPDATE t
SET object_type = 'PROCEDURE'
WHERE object_type <> 'VIEW'
AND rownum = 1;
COMMIT;
UPDATE t
SET object_type = 'FUNCTION'
WHERE object_type NOT IN ('VIEW','PROCEDURE')
AND rownum = 1;
COMMIT;
DELETE FROM t
WHERE object_type = 'PROCEDURE'
AND rownum = 1;
COMMIT;
SELECT versions_xid, versions_startscn,
versions_endscn, versions_operation, rowid, owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE (rowid = 'AAAPHdAAGAAABgqAAA'
OR object_type = 'FUNCTION');
SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN 5610589 AND 5610595
WHERE (rowid = 'AAAPHdAAGAAABgqAAA'
OR object_type = 'FUNCTION');
Flashback Version Query
SELECT <columns>
FROM <schema_name.table_name>
VERSIONS BETWEEN TIMESTAMP <minimum_scn> AND <maximum_scn>
[WHERE <column_filter>]
[GROUP BY <non-aggregated_columns>]
[HAVING <group filter>
[ORDER BY <position_numbers_or_column_names>]
conn uwclass/uwclass@pdbdev
CREATE TABLE t AS
SELECT owner, object_name, object_type
FROM all_objects
WHERE 1=2;
desc t
DECLARE
CURSOR fvq_cur IS
SELECT owner, object_name, object_type
FROM all_objects
WHERE rownum < 11;
BEGIN
FOR r IN fvq_cur LOOP
INSERT INTO t
VALUES r;
COMMIT; -- this is a bad practice done here intentionally
END LOOP; -- do not code incremental commits
END;
/
set linesize 141
col owner format a6
SELECT * FROM t;
SELECT versions_xid, versions_startscn,
versions_endscn, versions_operation, owner, object_name, object_type
FROM t
VERSIONS BETWEEN
TIMESTAMP MINVALUE AND MAXVALUE;
UPDATE t
SET object_type = 'VIEW'
WHERE object_type <> 'VIEW'
AND rownum = 1;
COMMIT;
UPDATE t
SET object_type = 'PROCEDURE'
WHERE object_type <> 'VIEW'
AND rownum = 1;
COMMIT;
UPDATE t
SET object_type = 'FUNCTION'
WHERE object_type NOT IN ('VIEW','PROCEDURE')
AND rownum = 1;
COMMIT;
DELETE FROM t
WHERE object_type = 'PROCEDURE'
AND rownum = 1;
COMMIT;
SELECT versions_xid, versions_startscn,
versions_endscn, versions_operation, rowid, owner, object_name, object_type
FROM t
VERSIONS BETWEEN
TIMESTAMP MINVALUE AND MAXVALUE;
SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN
TIMESTAMP MINVALUE AND MAXVALUE
WHERE (rowid = 'AAAPHdAAGAAABgqAAA'
OR object_type = 'FUNCTION');
SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN TIMESTAMP SYSDATE-5/60
AND SYSDATE
WHERE (rowid = 'AAAPHdAAGAAABgqAAA')
OR (object_type = 'FUNCTION');