Previous | Next | Contents | Index | Navigation | Glossary | Library |
For example, NULLs are always permitted in the shadow table. All columns in the shadow table have the same data types and sizes as their counterparts in the audited table.
The name of the shadow table is the first 26 characters of the original table name plus the suffix "_A" (Audit).
SQL> DESCRIBE AUDIT_DEMO NAME NULL? TYPE ------------------------------- -------- ---- PRIMARY_KEY NUMBER(5) VALUE_ONE VARCHAR2(5) VALUE_TWO VARCHAR2(5) VALUE_THREE VARCHAR2(5)
Its shadow table is as the following (assuming you audit all of your table columns):
SQL> DESCRIBE AUDIT_DEMO_A NAME NULL? TYPE ------------------------------- -------- ---- AUDIT_TIMESTAMP NOT NULL DATE AUDIT_TRANSACTION_TYPE NOT NULL VARCHAR2(1) AUDIT_USER_NAME NOT NULL VARCHAR2(100) AUDIT_TRUE_NULLS VARCHAR2(250) PRIMARY_KEY NUMBER VALUE_ONE VARCHAR2(5) VALUE_TWO VARCHAR2(5) VALUE_THREE VARCHAR2(5)
This trigger calls a stored procedure to compare each column being audited to see if its value is changing. If so, the procedure saves the previous (old) value to the shadow table.
Auditing creates one row in the shadow table for each audited transaction against the table; thus, a single row in the shadow table represents all old values for all changed columns on that transaction.
The data is not compressed, since a table uses only one byte for a NULL, and AuditTrail represents all unchanged values as NULLs in the shadow table ("sparse" format).
The audit trigger names contain the first 26 characters of the audited table name plus "_AI", "_AU" or "_AD", where one of I, U or D indicates Insert, Update or Delete, respectively. Likewise, the audit procedure names use the first 26 characters of the table name plus "_AIP", "_AUP" or "_ADP". Your table names must be unique within the first 26 characters.
The view name contains the first 26 characters of the audited table name plus "_AC#" or "_AV#" where C or V indicates the type of view and # indicates a number. Due to limitations in creation size, the shadow table columns may need to be broken into multiple views, which are numbered sequentially.
Each view allows slightly different access to the data. One allows the user to reconstruct the value for a row at a given time (_AC), while the other provides simple access to when a value was changed (_AV).
For our example table, the _AV1 and _AC1 views are created as follows:
SQL> DESCRIBE AUDIT_DEMO_AV1 NAME NULL? TYPE ------------------------------- -------- ---- AUDIT_TIMESTAMP DATE AUDIT_TRANSACTION_TYPE VARCHAR2(1) AUDIT_USER_NAME VARCHAR2(100) AUDIT_TRUE_NULLS VARCHAR2(250) PRIMARY_KEY NUMBER VALUE_ONE VARCHAR2(5) VALUE_TWO VARCHAR2(5) VALUE_THREE VARCHAR2(5)
SQL> DESCRIBE AUDIT_DEMO_AC1 NAME NULL? TYPE ------------------------------- -------- ---- AUDIT_TIMESTAMP DATE AUDIT_TRANSACTION_TYPE VARCHAR2(1) AUDIT_USER_NAME VARCHAR2(100) PRIMARY_KEY NUMBER VALUE_ONE VARCHAR2(5) VALUE_TWO VARCHAR2(5) VALUE_THREE VARCHAR2(5)
SQL> INSERT INTO AUDIT_DEMO VALUES (1,'A','A','A'); SQL> INSERT INTO AUDIT_DEMO VALUES (2,'X','X','X'); SQL> SELECT PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2, VALUE_THREE VAL_3 FROM AUDIT_DEMO; KEY VAL_1 VAL_2 VAL_3 ---- ----- ----- ----- 1 A A A 2 X X X
SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='B' WHERE PRIMARY_KEY = 1; KEY VAL_1 VAL_2 VAL_3 ---- ----- ----- ----- 1 B A A 2 X X X
SQL> UPDATE AUDIT_DEMO SET VALUE_TWO ='B' WHERE PRIMARY_KEY = 1; KEY VAL_1 VAL_2 VAL_3 ---- ----- ----- ----- 1 B B A 2 X X X SQL> UPDATE AUDIT_DEMO SET VALUE_THREE ='B' WHERE PRIMARY_KEY = 1; SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='Y' WHERE PRIMARY_KEY = 2; SQL> UPDATE AUDIT_DEMO SET VALUE_ONE = NULL WHERE PRIMARY_KEY = 1; SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='C' WHERE PRIMARY_KEY = 1;
After our two inserts and six updates, the final values in the audited table are:
KEY VAL_1 VAL_2 VAL_3 ---- ----- ----- ----- 1 C B B 2 Y X X
The final values in the corresponding shadow table are as follows. A row in the shadow table represents the state of the audited row before the audited row was changed. Note that if a value in a row doesn't change during the transaction, the shadow table records a null for that value in that transaction.
In our example, the first two rows in the shadow table represent the state where there was no data for our two audited rows before they were inserted. The "prior values" are null values for the two insert transaction (type I) rows. Similarly, when we update the first value of row 1 to be the value B instead of A, the shadow table records the value A in its third row:
SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME, AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME, PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2, VALUE_THREE VAL_3, AUDIT_TRUE_NULLS FROM AUDIT_DEMO_A; TIME TYPE NAME KEY VAL_1 VAL_2 VAL_3 AUDIT_TRUE_NULLS -------- ---- ------ ---- ----- ----- ----- ---------------- 11:08:16 I FND60 1 11:08:40 I FND60 2 11:18:40 U FND60 1 A 11:20:12 U FND60 1 A 11:21:54 U FND60 1 A 11:22:15 U FND60 2 X 14:20:50 U FND60 1 B 14:21:15 U FND60 1 NYNN 8 rows selected.
Given the current values of the row in the audited table, you can trace the changes made to the row by backing up through the corresponding rows in the shadow table.
In our example table, we made two insert and six update transactions, so we see those eight transactions in our shadow table. In the last row, the NYNN indicates that the value in the second table column (VALUE_ONE) has changed from an actual null value (the Y) rather than being an unchanged value (represented by null in the shadow table).
The following two views provide further ways of examining your audited data.
The rows with a transaction type of C in the view indicate the current value of the row when the data was selected (the view is a join between the shadow table and the audited table, so the current value row reflects the current state of the audited table).
The _AC view provides a "filled-in" version of the data, where unchanged values appear instead of being represented by null values. You can order this view by the primary key (rather than by timestamp), so all rows in the shadow table that correspond to a single audited row appear together, with a secondary ordering by timestamp.
SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME, AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME, PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2, VALUE_THREE VAL_3 FROM AUDIT_DEMO_AC1 ORDER BY PRIMARY_KEY, AUDIT_TIMESTAMP; TIME TYPE NAME KEY VAL_1 VAL_2 VAL_3 -------- ---- ---------- ---- ----- ----- ----- 11:08:16 I FND60 1 A A A 11:18:40 U FND60 1 B A A 11:20:12 U FND60 1 B B A 11:21:54 U FND60 1 B B B 14:20:50 U FND60 1 B B 14:21:15 U FND60 1 C B B 17:53:34 C 1 C B B 11:08:40 I FND60 2 X X X 11:22:15 U FND60 2 Y X X 17:53:34 C 2 Y X X 10 rows selected.
Attention: If the changes to your audited table occur faster than one change per second (that is, more frequently than the one-second granularity provided by SYSDATE), you may see "blurring" of records -- more than one record per transaction -- in the _AC view because of joins used in this view. However, the shadow table itself remains correct for your transactions, and you can resolve those transactions using the shadow table directly.
The _AV1 view provides a more sparse view of the audit data, ordered by timestamp:
SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME, AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME, PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2, VALUE_THREE VAL_3, AUDIT_TRUE_NULLS FROM AUDIT_DEMO_AV1; TIME TYPE NAME KEY VAL_1 VAL_2 VAL_3 AUDIT_TRUE_NULLS -------- ---- ------ ---- ----- ----- ----- ---------------- 11:08:16 I FND60 1 11:08:40 I FND60 2 11:18:40 U FND60 1 A 11:20:12 U FND60 1 A 11:21:54 U FND60 1 A 11:22:15 U FND60 2 X 14:20:50 U FND60 1 B 14:21:15 U FND60 1 NYNN 17:58:31 C 1 C B B 17:58:31 C 2 Y X X 10 rows selected.
Here is an example of how you might use a view to determine who changed a particular value and when:
SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME, AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME FROM AUDIT_DEMO_AV1 WHERE PRIMARY_KEY = 1 AND VALUE_ONE = 'B'; TIME TYPE NAME -------- ---- ------ 14:20:50 U FND60
Similarly, you might want to determine who changed a value to null and when:
SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME, AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME FROM AUDIT_DEMO_AV1 WHERE PRIMARY_KEY = 1 AND VALUE_ONE IS NULL AND SUBSTR(AUDIT_TRUE_NULLS,2,1) = 'Y'; TIME TYPE NAME -------- ---- ------ 14:21:15 U FND60
Setting Up Release 11 AuditTrail
Reporting on Release 11 Audit Information
Disabling AuditTrail and Archiving Audit Data
Previous | Next | Contents | Index | Navigation | Glossary | Library |