ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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');
