This section provides information about the Data Archival feature used in the OFSAA Data Foundation applications.
Topics:
· Implementation of Data Archival by OFSAA
The OFSAA data model, along with the active data stores Personally Identifiable Information (PII) of Data Subjects who are no longer actively using the financial services, which is a liability.
To implement this invisibility of data at the row level, Oracle Database 12c has introduced a new feature called Row Archival.
· The Row Archival feature is simple and effective to use as opposed to the traditional approach, which requires storage and maintenance of historical tables.
· This feature enables to archive records based on given criteria within the account table. The criteria can be, for example, the Account Close Date of the Data Subject.
· This archived data can be viewed or made hidden by setting a session parameter.
To archive Party records, which are closed for more than seven years, follow these steps:
1. To enable Row Archival on the DIM_PARTY table, run the following command:
ALTER TABLE DIM_PARTY ROW ARCHIVAL
2. Run the Row Archival Update statement periodically, to search for the account Party records which are closed for more than seven years. Run the following command:
UPDATE DIM_PARTY SET ora_archive_state='1' where trunc(months_between(SYSDATE, d_closed_date)/12)>7
In the above query, the details are as follows:
§ ora_archive_state is a hidden column that is created after the ROW ARCHIVAL is set on the table.
§ SET ora_archive_state='1' is part of the query archives the records which meet the criteria of seven years of account closure.
§ Hidden rows are available for the Select/Update queries only after the ora_archive_state column is disabled.
To make hidden rows visible for a session, run the following command:
ALTER SESSION SET ROW ARCHIVAL VISIBILITY=ALL;
To make the rows invisible, run the following command:
ALTER SESSION SET ROW ARCHIVAL VISIBILITY=ACTIVE;