Install the database by completing the Installing Oracle Database 11g Release 2 on Linux tutorial.

Download and unzip the flada.zip file into your working directory.

Note: the files for this OBE are intended for a Linux environment. Two files: flada_setup.sql and flada_tbs2.sql must be modified to work in a Windows environment by changing the datafile specification in the CREATE TABLESPACE command to a Windows file name.

Using SQL*Plus, connect to the database as the SYS user and run the flada_setup.sql script from the directory when the files are located.

sqlplus / as sysdba
@flada_setup.sql

The setup script creates a small tablespace and unlocks the HR user with the HR password. The password is case sensitive by default.

To give the HR user administrative privileges for creating, maintaining and dropping flashback data archives, execute the following command as the SYS user:

GRANT FLASHBACK ARCHIVE ADMINISTER TO hr;

In SQL*Plus, connect as the HR user with the HR password. Note: the password needs to be in upper case.

connect hr/HR

To create a flashback data archive, execute the following command:

CREATE FLASHBACK ARCHIVE fla1
TABLESPACE fla_tbs1
QUOTA 10M
RETENTION 1 YEAR
/

Space requirements depend on your chosen retention period and the activity of the tracked table.

To enable this flashback data archive for the EMPLOYEES table, execute the following command:

ALTER TABLE hr.employees
FLASHBACK ARCHIVE fla1
/

To view and increase the salary of Mr. Fox three times by 1000, execute the flada_dml.sql script. This produces activity in the flashback data archive.

@flada_dml.sql

Query the salary of Mr. Fox to confirm the current value. Press [Enter] to continue.

On the previous screenshot you see the current salary. Press [Enter] to make a series of updates.

On the previous screenshot you see committed updates. Press [Enter] to re-query the updated record.

Press [Enter] to continue.

On the previous screenshot you see the newly updated salary of Mr. Fox. Press [Enter] to exit the script.

You can view records as of any point in time, since the flashback data archive was created. See the "Viewing Data Dictionary Information about Flashback Data Archives" lesson for an example on how to query the "created" time.

Choose a time after the creation of the flashback data archive and before you executed the erroneous DML. To view Mr. Fox's employee record as of that time, execute the following query (you can replace '10' MINUTE with your chosen historic date, format examples: '50' SECOND, '10' DAY, '5' MONTH):

SELECT employee_id, last_name, salary
FROM hr.employees
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE last_name = 'Fox'
/

You can check that the flashback archive is being utilized by examining the query execution plan. Execute the flada_explainplan.sql script:

@flada_explainplan 

Note that historical information is being retrieved from the SYS_FBA_HIST_72390 table (the numeric identifiers in your table names may differ).

You realize that the recent salary updates were mistakes. To revert to the original values for your chosen historic date (for example, ten minutes ago), execute the following command (replace '10' MINUTE with your chosen historic date):

UPDATE hr.employees
SET salary =
(SELECT salary FROM hr.employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE last_name = 'Fox')
WHERE last_name = 'Fox'
/

Commit the UPDATE and query Mr. Fox's salary to confirm it has reverted back to the original value.

On the previous screenshot you see the UPDATE command, which uses a historic row to revert to the value from ten minutes ago. (In this test environment, you are most likely working with minutes. However, in a production environment, you might use DAY, MONTH or YEAR.)

Continuing in SQL*Plus, execute the following query:

SELECT email, last_name, salary
FROM hr.employees
WHERE email LIKE 'T%'
/

You will notice that the email addresses are a concatenation of the first initial and last name for each person.

Execute the following UPDATE and COMMIT commands to append @mycompany.com to all of the email addresses:

UPDATE hr.employees SET email = email||'@mycompany.com'
/
COMMIT
/

Re-query the table to confirm the changes.

Imagine that a new email address standard is being implemented. For a while the current email addresses will be stored in the old_email column while the original email column will be updated to contain the new addresses. Add the required column using the following statement:

ALTER TABLE hr.employees
ADD (old_email varchar2(30))
/

Populate the newly added column with the current email addresses:

UPDATE hr.employees
SET old_email = email
/
COMMIT
/

In addition, another new column needs to be implemented to store a computed value for the employee's total compensation. Add the new column using the following statement:

ALTER TABLE hr.employees
ADD (total_comp number(8,2))
/

Populate the new column with the computed value for TOTAL_COMP:

UPDATE hr.employees
SET total_comp = (1+NVL(commission_pct,0))*salary
/
COMMIT
/

Execute the following query to see all of the versions of Mr Fox's record since the beginning of the tutorial:

SELECT last_name, email, old_email, salary, total_comp, versions_endtime
FROM hr.employees
VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE) AND SYSTIMESTAMP
WHERE last_name = 'Fox'
ORDER BY versions_endscn
/

Note the use of the versions_endtime and versions_endscn pseudocolumns in the query. versions_endtime shows the time at which that version of the record expired. You can see that versions_endtime is NULL in the last record as that is the current view. versions_endscn provides the correct order of transactions even if the time cannot be differentiated.

While the results of the previous query show how Mr Fox's record changed over time, it may be useful to also know how the employees table changed as well. Execute the following statements to gather this information:

SELECT * FROM tab
/

Note the name of the SYS_FBA_DDL_COLMAP_nnnnn table (the numeric identifiers in your table names may differ).

Query the table you identified in the previous step to determine the System Change Number (SCN) ranges that define the existance of each column in the table:

SELECT column_name, startscn, endscn
FROM SYS_FBA_DDL_COLMAP_nnnnn
/

On the previous screenshot the NULL values for endscn indicate that the column still exists in the table. You can convert the SCNs into timestamps using the SCN_TO_TIMESTAMP function. For example:

SELECT SCN_TO_TIMESTAMP(nnnnnn)
FROM DUAL
/

You now have all the information you require to re-construct a complete history of the data.

From your SQL*Plus session, connect to the database as the SYS user.

connect / as sysdba

Execute the flada_list1.sql script to explore various data dictionary views relating to flashback data archives.

@flada_list1.sql

The script will pause at various times. Examine the output from each stage before pressing [Enter] to continue.

The first query in the script lists the data dictionary views relating to flashback data archives.

The DBA_FLASHBACK_ARCHIVE data dictionary contains information about flashback data archives including the time of creation.

The DBA_FLASHBACK_ARCHIVE_TS data dictionary view lists the tablespaces associated with flashback data archives.

The DBA_FLASHBACK_ARCHIVE_TABLES data dictionary view shows the relationship between data tables and their corresponding "history" tables.

A non-DBA user, such as the HR user, will only have acces to the USER data dictionary views.

Using SQL*Plus, connect to the database as the HR user.

sqlplus hr/HR

If a retention period is defined, data in the flashback data archive is automatically purged in line with the retention policy. However, you can also explicitly purge data with the following command:

ALTER FLASHBACK ARCHIVE fla1
PURGE BEFORE TIMESTAMP
(SYSTIMESTAMP - INTERVAL '2' MINUTE)
/

In this case, data older than two minutes is purged.

Execute the flada_tbs2.sql script to create an additional 10 MB tablespace.

@flada_tbs2.sql

As the HR user, add 5 MB of the FLA_TBS2 tablespace to the FLA1 flashback data archive. Execute the following command:

connect hr/HR

ALTER FLASHBACK ARCHIVE fla1
ADD TABLESPACE fla_tbs2
QUOTA 5M
/

All the tables in a specific flashback data archive have the same retention time. To change the retention time of the FLA1 flashback data archive to two years, execute the following command:

ALTER FLASHBACK ARCHIVE fla1
MODIFY RETENTION 2 YEAR
/

After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. To disable the flashback data archive for the HR.EMPLOYEES table, execute the following command:

ALTER TABLE hr.employees NO FLASHBACK ARCHIVE
/

To drop the FLA1 flashback data archive, execute the following command:

DROP FLASHBACK ARCHIVE fla1
/

Dropping a flashback data archive includes dropping the internal tamper-proofed history table. This internal table cannot be directly dropped by you due to auditing and security requirements.

Dropping a flashback data archive does not drop the tablespaces in which they are stored, because the tablespaces might contain other data.

From your SQL*Plus session, connect to the database as the SYS user.

connect / as sysdba

To drop the tablespaces and lock the HR user, execute the flada_cleanup.sql script.

@flada_cleanup.sql