| The flashback versions query is another new feature introduced with the 10g Database that will allow you to retrieve a history of changes for a specified set of rows within a table. This new feature expands the flashback query technology first introduced in Oracle 9i. Instead of specifying a time or SCN to enable flashback query for analysis, you can now use the VERSIONS_BETWEEN clause to retrieve all the versions of a specified set of rows that exist or ever existed between the time the query was issued and a point back in time that you must specify. The VERSIONS_BETWEEN clause eliminates the need to flashback in time to review historical row modifications over time. The flashback versions query feature, like Flashback Table, utilizes data from the undo segments, so you must configure your instance to use automatic undo management. The amount of data that you can retrieve with the VERSIONS_BETWEEN clause is limited by the setting of the UNDO_RETENTION initialization parameter. Because the UNDO_RETENTION parameter determines your row history, you should set this parameter based on your needed timeline to utilize flashback versions query. Also, you can use the RETENTION GUARANTEE clause to ensure that you will retain all critical undo data when using flashback versions query. Along with the row history, the flashback versions query feature also returns specific data about each transaction using its own pseudocolumns. You can include these pseudocolumns with your query to provide a greater level of validity for the row history. The new pseudocolumns that flashback versions query can use include the following: VERSIONS_STARTIME. Starting timestamp of the row version. VERSIONS_ENDTIME. Timestamp when the row version expired. VERSIONS_STARTSCN. Starting SCN of the row version. VERSIONS_ENDSCN. SCN when the row version expired. VERSIONS_XID. Identifier of the transaction that created the row version. VERSIONS_OPERATION. Row operation used (I for insert, U for update, and D for delete).
Flashback Versions Query in Action Using the flashback versions query is simple, but any user who uses it will require the SELECT and FLASHBACK object privileges. You can use one of the following tools to retrieve row history using flashback versions query: Following is a brief example of using the VERSION_BETWEEN command via SQL: 1. | Create your sample data and grant necessary privileges:
SQL> create table flashtest as select * from hr.employees; Table created. SQL> grant select, flashback on flashtest to stroupe;
| 2. | Initiate modifications to rows in the table:
SQL> select first_name, email from flashtest where department_id = 30; FIRST_NAME EMAIL -------------------- ------------------------- Den DRAPHEAL Alexander AKHOO Shelli SBAIDA Sigal STOBIAS Guy GHIMURO Karen KCOLMENA SQL> update flashtest set email = EMAIL||'@HRDEPT' where department_id = 30; commit. SQL> select first_name, email from flashtest where department_id = 30; FIRST_NAME EMAIL -------------------- ------------------------- Den DRAPHEAL@HRDEPT Alexander AKHOO@HRDEPT Shelli SBAIDA@HRDEPT Sigal STOBIAS@HRDEPT Guy GHIMURO@HRDEPT Karen KCOLMENA@HRDEPT SQL> delete flashtest where department_id = 30; 6 rows deleted. SQL> commit;
| 3. | Review the row history using the VERSIONS_BETWEEN clause:
SQL> select versions_startscn, versions_endscn, versions_xid, versions_operation, first_name, email from flashtest VERSIONS BETWEEN SCN MINVALUE and MAXVALUE where department_id = 30; VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V FIRST_NAME EMAIL ----------------- --------------- ---------------- - ------------------- --------------- 929764 05002300BF000000 D Karen KCOLMENA@HRDEPT 929764 05002300BF000000 D Guy GHIMURO@HRDEPT 929764 05002300BF000000 D Sigal STOBIAS@HRDEPT 929764 05002300BF000000 D Shelli SBAIDA@HRDEPT 929764 05002300BF000000 D Alexander AKHOO@HRDEPT 929764 05002300BF000000 D Den DRAPHEAL@HRDEPT 929683 929764 0A002E0076000000 U Karen KCOLMENA@HRDEPT 929683 929764 0A002E0076000000 U Guy GHIMURO@HRDEPT 929683 929764 0A002E0076000000 U Sigal STOBIAS@HRDEPT 929683 929764 0A002E0076000000 U Shelli SBAIDA@HRDEPT 929683 929764 0A002E0076000000 U Alexander AKHOO@HRDEPT 929683 929764 0A002E0076000000 U Den DRAPHEAL@HRDEPT 929683 Den DRAPHEAL 929683 Alexander AKHOO 929683 Shelli SBAIDA 929683 Sigal STOBIAS 929683 Guy GHIMURO 929683 Karen KCOLMENA 18 rows selected.
|
You can also easily use Enterprise Manager to display the row history using flashback versions query. Following is a brief example of flashback versions query using OEM: 1. | After you have created your table and initiated changes on the rows (as shown in steps 1 and 2 of the SQL flashback versions query), review your table via OEM by selecting from the OEM home page (choose Administration and Tables under the Schema subheading). After you enter your schema name, select the correct table name and then select the View Data action in the Actions drop-down list and click Go. Here, you can click the Refine Query button to return a specific result set (see Figure 17.18).
Figure 17.18. Viewing table data from 10g OEM. 
| 2. | Choose your recovery method by selecting the Maintenance tab and choosing Perform Recovery under the Backup/Recovery subheading.
| 3. | Via the Recovery wizard, select Tables as the object type, select Flashback Existing Tables from the Recovery Type page, and then click next.
| 4. | To evaluate the row history, select the schema and table name to evaluate. See Figure 17.19.
Figure 17.19. Specifying a table for flashback versions query from 10g OEM Recovery wizard. 
| 5. | Using the flashback query filter, select the table columns and the correct predicate value for your analysis (see Figure 17.20).
Figure 17.20. Specifying filter conditions for flashback versions query from 10g OEM Recovery wizard. 
| 6. | When you have the correct filter information, you will see the OEM flashback versions query result set, as shown in Figure 17.21. From here you can choose the row or rows if you wish to use the Flashback Table option for point-in-time recovery.
Figure 17.21. Reviewing the Flashback Versions Query Result Set from 10g OEM Recovery wizard. 
| 7. | To ensure that OEM is using the correct flashback versions query information, click the Show Flashback Versions Query SQL button in the screen shown in Figure 17.20 (refer to step 5). This displays the screen shown in Figure 17.22.
Figure 17.22. Flashback Versions Query SQL from 10g OEM Recovery wizard. 
|
Troubleshooting Flashback Versions Query The only major requirement to utilizing the flashback versions query feature is to set up and configure automatic undo management to the appropriate size for UNDO_RETENTION. That said, there are a few specific limitations for 10g Release 1, outlined here: The flashback versions query feature only retrieves committed modifications of the rows. Any row modifications that are not committed, including current active transactions, are not supported by the flashback versions query. You cannot use the flashback versions query feature to retrieve data from a view, but you can use the VERSIONS_BETWEEN clause in a view definition. You cannot use the flashback versions query feature to retrieve data across certain DDL commands. The VERSIONS_BETWEEN clause will not show row history after it reaches a time in the past when the table specification was changed. Currently in 10g Release 1, you cannot use the flashback versions query on any table that has a block size higher than 8KB. If you have a table that uses 16KB or 32KB block sizes, you will receive an ORA-600 error message when using flashback versions query. Oracle support is tracking this issue as bug 3469992. If you migrate your 9i database to 10g (even using the Migration Assistant), you will need to run the following SQL to fully utilize 10g flashback versions query: SQL> delete from smon_scn_time where orig_thread <> 0;
After migration to 10g, you cannot fully utilize flashback up to the UNDO_RETENTION setting. See MetaLink Note #301502.1 for more information about this limitation. |
