Shulou(Shulou.com)06/01 Report--

The function of standard Flashback Query is to query the record information saved in the object at a certain time point in the past. Between the current time and a specified time point in the past, the object may have been modified many times. If you want to see every modification during this period, Oracle provides Flashback Query Version.

Flashback Version Query is also called as a SELECT clause, and its usage is no more complicated than AS OF, similar to it, just add VERSIONS BETWEEN TIMESTAMP[/SCN] START AND END clause to the standard SELECT query. VERSIONS BETWEEN allows you to view different versions of records in the UNDO table space for a specified period of time (note that only committed records are included). Records may have a one-to-many relationship in Version Query. For example, if some records have been modified many times and submitted separately, then when querying, if the modification operation is performed within a specified time period (or SCN), the result of each modification of the record will be displayed. This is more conducive to comparing the modifications to the data and seeing how the data changes.

Multiple pseudo-columns are provided in Version Query, as shown in the table.

table Flashback Query Version Query pseudo-column description

VERSIONS_STARTSCN VERSIONS_STARTTIME

SCN or time when the record was operated. If blank, it means that the record was created outside the query scope.

VERSIONS_ENDSCN

VERSIONS_ENDTIME

SCN or time when the record becomes invalid. If blank, it indicates that the current time of the record does not exist in the current table or has been deleted. It can be viewed in conjunction with the VERSIONS_OPERATION column. If the value of the VERSIONS_OPERATION column is D, it indicates that the column has been deleted. If the column is blank, it indicates that the record has no operation during this period.

VERSIONS_XID

Transaction ID of the operation

VERSIONS_OPERATION

Action performed on the row: I for INSERT, D for Delete, U for UPDATE

Tip: For an update operation on an index key, a version query might recognize it as two operations: Delete and INSERT

Related knowledge points:

What is a pseudocolumn? We all know that column names, column types, and other information must be specified when creating tables. These explicitly specified columns are standard columns that can be displayed by SELECT statements during queries. In addition, there is a column type that does not exist in the table definition. For example, you cannot see the definition of these columns when you view them through DESC TBLNAME, but you can query the contents of these columns through SELECT statements. This column is a pseudo-column provided by Oracle (for example, system functions such as SYSDATE are not pseudo-columns).

Pseudo-columns are not applicable to any query, and some columns can only be displayed in special query statements, such as the 6 columns provided in the above table, which can only be called when using Versions Query. In addition, there are some commonly used ROWNUM, LEVEL and so on.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.