闪回技术11g

闪回查询

SQL> GRANT FLASHBACK ON employees TO salapatiGrant succeeded.SQL>

Or you could use a statement like this:

SQL> GRANT FLASHBACK ANY TABLE TO salapati;Grant succeeded.SQL>

You can grant the Flashback Query object privilege (GRANT FLASHBACK ANY TABLE) on a table, view, or a materialized view.

Next, use the SELECT . . . AS OF query to retrieve Flashback data from the past.

SQL> SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP ('2008-09-02 08:00:00', 'YYYY-MM-DD HH:MI:SS') WHERE last_name = 'Alapati';

Once you confirm the validity of the accidentally deleted data, it's easy to reinsert the data by using the previous query as part of anINSERT statement, as shown here:

SQL> INSERT INTO employees SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2008-09-02 08:00:00', 'YYYY-MM-DD HH:MI:SS') WHERE last_name = 'Alapati';

闪回version查询

Using the versions clause in a query will get you multiple versions of the rows returned by the query. In the preceding syntax statement, you can use theVERSIONS clause as a part of your normalSELECT statement, with a BETWEEN clause appended to it. You can also specify anSCN or aTIMESTAMP clause. You must specify the start and end expressions by usingMINVALUE andMAXVALUE, which indicate the start time and end time of the interval for which you are seeking the different row versions. TheMINVALUE and theMAXVALUE are resolved to the time stamp or the SCN of the oldest and the most recent data that's available in the database, respectively.

You must ensure that the beginning and ending interval, framed by either SCNs or time stamps, don't go back beyond the time specified by theUNDO_RETENTION parameter. While you can actually specify a begin or an end time point that lies outside the interval spanned by theUNDO_RETENTION parameter, the query may not work.

Note that the AS OF clause is optional, and when you use it, the database will retrieve all the rows as of that particular SCN or time stamp. If theVERSIONS clause is used by itself, as inVERSIONS BETWEEN SCN MINVALUE and MAXVALUE, without using the optionalAS OF clause, the data is retrieved as of the current session. If you add theAS OF clause, as shown next, the data is retrieved as of a specified SCN or clock time:

VERSIONS BETWEEN SCN MINVALUE and MAXVALUE AS OF SCN 56789

Flashback Versions Query Pseudo-Columns

The output of a Flashback Versions Query is unlike the output of your typical SELECT statement. The output can show multiple versions of the same row, with a row representing each time the particular row was inserted, updated, or deleted. In addition to the column values you specify in the SELECT statement, Oracle will provide you with values for a set of pseudo-columns for each row version. These pseudo-columns provide metadata about the various row versions, including the type of operation, the begin and end time of the transaction, and so on. It is these pseudo-columns that tell you exactly when a row was modified and what was done to the row at that time.

Here is a brief explanation of each pseudo-column in the Flashback Versions Query output:

• VERSIONS_STARTSCN and VERSIONS_STARTTIME: These pseudo-columns tell you the SCN and time stamp when this particular row was first created. If the VERSIONS_STARTTIME is null, the row was created before the lower time boundary of the query.

• VERSIONS_ENDSCN and VERSIONS_ENDTIME: These pseudo-columns tell you when this particular row expired. If the VERSIONS_ENDTIME column value is null, it means that the row is either current or that it has been deleted.

• VERSIONS_OPERATION: This pseudo-column provides you with information about the type of DML activity that was performed on the particular row. The column has three possible values: I represents an insert, D a delete operation, and U an update operation.

• VERSIONS_XID: This pseudo-column displays the unique transaction identifier of the transaction that resulted in this row version.

If the version of a row was created before the MINVALUE or the beginning of the query, you can't capture the value for the starting time stamp or SCN, and your VERSIONS_STARTSCN and VERSIONS_STARTTIME pseudo-columns will be null—there won't be any history for this row in your undo segments.

The VERSIONS_ENDSCN and VERSIONS_ENDTIME pseudo-columns tell you when the row version expired. If the row version is still current at the time of your Flashback Versions Query, the VERSIONS_ENDSCN and VERSIONS_ENDTIME pseudo-columns will be null. Similarly, if the row version has been deleted from the table, you'll see a null value for these two pseudo-columns.