Oracle 12c, ILM Management Part-1,In-Database Archiving….

As with the databases tend to grow, there is going to be always a requirement to do something about that data which is no longer required for the daily usage. For example, in a telecom company, data related to the current month is the most useful data. So what you do to achieve such kind of data archiving? There are couple of ways actually for such kind of data management, like partitioning, Flashback Data Archive. but can there be a more easy way than maintaining partitions or creating a separate object like Flashback Data archive? The answer is given by the Oracle database 12c which has got two new features, In-database archiving and Temporal Validity for the better management and house keeping of the archive data. In this post, we shall see the in-database archiving.

In-database Archiving

This features is far easier to implement than the similar features like partitioning etc because, as the name says, it’s enabled within the same table itself and would be marking the table’s rows as “active” or “inactive” thus to decide that whether they would be visible in a query or not. Unlike partitioning etc. this feature works on the row level and based on the activity done on the table decides whether the rows are considered active or not.

To enable this feature, you would need to create your table using a new attribute ROW ARCHIVAL. This attribute, when used would be creating a pseudo column in the table with the name ORA_ARCHIVE_STATE which would contain either the value 0 or 1 where the value 1 means that the said row wasn’t active and can be archived. The value 0 means that the row is an active row and would be remaining in the table and also would be visible in any query done over the table. So let’s create one such table. shall we?

SQL> create table test(col1 number) row archival;
Table created.

Now, let’s enter some data in this table.

SQL> insert into test values(1);
1 row created.
SQL> c/1/2
1* insert into test values(2)
SQL> /
1 row created.
SQL> c/2/3
1* insert into test values(3)
SQL> /
1 row created.
SQL> c/3/4
1* insert into test values(4)
SQL> commit;
Commit complete.

Since this is a new table, the entire data of it is going to be considered as active only and we can confirm this by querying the table along with the pseudo column ORA_ARCHIVE_STATE and the normal columns of it.

SQL> select ora_archive_state, col1 from test;
ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
COL1
----------
0
1
0
2
0
3
SQL> column ora_archive_state format a20
SQL> /
ORA_ARCHIVE_STATE COL1
-------------------- ----------
0 1
0 2
0 3
SQL> l
1* select ora_archive_state, col1 from test
SQL> /
ORA_ARCHIVE_STATE COL1
-------------------- ----------
0 1
0 2
0 3

Since the value shown in the column ORA_ARCHIVE_STATE is 0, this confirms that the data in the table is active.

Now, let’s make the very first row inactive by updating the ORA_ARCHIVE_STATE column value to 1 for its row using the new package DBMS_ILM using its function ARCHIVESTATENAME.

SQL> update test set ora_archive_state=dbms_ilm.archivestatename(1) where col1=1;
1 row updated.
SQL> commit;
Commit complete.

Since this row is marked as inactive, a query on the table won’t display it by default.

SQL> select col1 from test;
COL1
----------
2
3

As a default setting, the inactive rows are not displayed in the output.To see all the rows, including the inactive one too, you would need to make it visible explicitly using the session level setting of the parameter ROW ARCHIVAL VISIBILITY to the value ALL.

SQL> alter session set row archival visibility=all;
Session altered.
SQL> select col1 from test;
COL1
----------
1
2
3
SQL> select ora_archive_state, col1 from test;
ORA_ARCHIVE_STATE COL1
-------------------- ----------
1 1
0 2
0 3

What would happen if you would update this new column ORA_ARCHIVE_STATE to any other value than 1? Well, that’s going to make the same impact as the value 1 and would mark the rows as inactive only. This means, any value other than 0 would be considered as a marker for inactivity only.

SQL> update test set ora_archive_state=2 where col1=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select ora_archive_state, col1 from test;
ORA_ARCHIVE_STATE COL1
-------------------- ----------
1 1
2 2
0 3

And if you are not happy with this and want to make the table free from such in-database archiving, you can just disable the same from the table like below. But since we made the table using the SYS, this won’t work for us!

SQL> alter table test no row archival;
alter table test no row archival
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

Had it been some other user, with the above command, the rows no longer hadn’t been marked as active or inactive and also, the pseudo column would had been dropped as well.

There are some restrictions to use the feature as well like the one you saw just above. To know more about the feature and about the restrictions, see the links given in the reference section below.

References:

http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFT236

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154

In the part-2, I shall be discussing about the Temporal Validity! Stay tuned!