Table 7-3 shows the pseudocolumns available with Flashback
Version Queries.
Pseudocolumn Description
VERSIONS_
START{SCN|TIME}
The starting SCN or timestamp when the change was made to
the row.
VERSION_END{SCN|TIME} The ending SCN or timestamp when the change was no longer
valid for the row. If this is NULL, either the row version is still
current or the row was deleted.
VERSIONS_XID The transaction ID of the transaction that created the row version.
VERSIONS_OPERATION The operation performed on the row (I=Insert, D=Delete,
U=Update).
TABLE 7-3 Flashback Version Query Pseudocolumns
234 Oracle Database 11g DBA Handbook
The HR user runs a Flashback Version Query to see the changes to any key columns in
HR.EMPLOYEES for the two employees with IDs 195 and 196:
SQL> select versions_startscn startscn, versions_endscn endscn,
2 versions_xid xid, versions_operation oper,
3 employee_id empid, last_name name, manager_id mgrid, salary sal
4 from hr.employees
5 versions between scn 4011365 and 4011508
6 where employee_id in (195,196);
STARTSCN ENDSCN XID OPER EMPID NAME MGRID SAL
--------- --------- ---------------- ---- ----- -------- ----- ----------
4011507 1100120025000000 U 195 Jones 100 4032
4011463 4011507 0E001A0024000000 U 195 Jones 100 3360
4011463 195 Jones 123 2800
4011463 0E001A0024000000 D 196 Walsh 124 3100
4011463 196 Walsh 124 3100
The rows are presented with the most recent changes first. Alternatively, HR could have filtered
the query by TIMESTAMP or displayed the TIMESTAMP values, but either can be used in a Flashback
Query or Flashback Table operation, if required later.
Pages:
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395