About In-database archiving
Older,
rarely accessed data may not be used or processed by applications. However
there may be regulatory requirements that specify that the data must be
retrievable. For such a need, Oracle Database 12c introduces the ability to
archive older records within the table itself.
This is
known as in-database archiving. When a table is enabled for such archiving,
each of its record has a archival state property. By default, a record archived
in this manner is not seen by applications. But archived data can be queried by enabling a
session parameter ROW_ARCHIVAL_VISIBILITY.
Examples:
Enabling
archival for a table
Consider the following employees table, which is contained
in a pluggable database named hr.
It has a 10 records.
SQL> select * from employees;
ID NAME START_DAT EMPNO
---------- --------------------
--------- ----------
1 EMP_1 02-JAN-00 12346
2 EMP_2 03-JAN-00 12347
3 EMP_3 04-JAN-00 12348
4 EMP_4 05-JAN-00 12349
5 EMP_5 06-JAN-00 12350
6 EMP_6 07-JAN-00 12351
7 EMP_7 08-JAN-00 12352
8 EMP_8 09-JAN-00 12353
9 EMP_9 10-JAN-00 12354
10 EMP_10 11-JAN-00 12355
10 rows selected.
To enable row level archiving on this table, issue the
ALTER TABLE … ROW ARCHIVAL statement as shown.
SQL> alter table emp row
archival;
Table altered.
The above alter table statement changes the table structure
to add a hidden column to track the archive state of each row. This column can
be selected from sql explicitly:
SQL> select id, name,
ora_archive_state from employees;
ID NAME ORA_ARCHIVE_STATE
---------- --------------------
--------------------
1 EMP_1 0
2 EMP_2 0
3 EMP_3 0
4 EMP_4 0
5 EMP_5 0
6 EMP_6 0
7 EMP_7 0
8 EMP_8 0
9 EMP_9 0
10 EMP_10 0
10 rows selected.
The value is 0 by default which denotes active status. All
rows are returned at this time.
Setting
the archival state for a record to archived
An update can be issued to set the ora_archive_state column
to a non-zero (archived) status.
A non-zero archival state means that the record is in
archived state, and it wont be visible to SQL. The records with an archived
status are hidden from sql as seen below. Only 8 out of the 10 total records
are listed. The 8 are the records which stil have an active status, following
the update. Even a count(*) would only count 8 records.
SQL> update employees set
ora_archive_state=1 where id in (1,2);
2 rows updated.
SQL> select id, name,
ora_archive_state from employees;
ID NAME ORA_ARCHIVE_STATE
---------- --------------------
--------------------
3 EMP_3 0
4 EMP_4 0
5 EMP_5 0
6 EMP_6 0
7 EMP_7 0
8 EMP_8 0
9 EMP_9 0
10 EMP_10 0
8 rows selected.
Examining the explain plan for the above sql, its seen that
a WHERE (WHERE ora_archivestate=’0’) predicate filter was automatically added
to the SQL.
Over-riding
the default invisibility of archived state records.
If its desired to view archived records , issue an ALTER
SESSION statement to alter the ROW ARCHIVAL VISIBILITY property to ALL (the
default is ACTIVE) and query the table as before. For example, see below how
the select issued after the ALTER SESSION returns inactive as well as active
rows.
SQL> alter session set ROW
ARCHIVAL VISIBILITY=all;
Session altered.
SQL> select id, name,
ora_archive_state from employees;
ID NAME ORA_ARCHIVE_STATE
---------- --------------------
--------------------
1 EMP_1 1
2 EMP_2 1
3 EMP_3 0
4 EMP_4 0
5 EMP_5 0
6 EMP_6 0
7 EMP_7 0
8 EMP_8 0
9 EMP_9 0
10 EMP_10 0
10 rows selected.
Its seen that the plan does not have the WHERE filter now.
Disabling
archival for a table
To disable issue the ALTER TABLE <TABLE> NO ROW
ARCHIVAL command. The column created to track archival status will be dropped.
SQL> alter table employees no
row archival;
Table altered.
No comments:
Post a Comment