12C: IN DATABASE ARCHIVING

In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.

Overview:

-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm

Implementation :

-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name

CON_NAME
------------------------------
PDB1

SQL> set sqlprompt PDB1>

PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba  to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;

conn uilm/oracle@em12c:1523/pdb1
sho con_name

CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
2 two             0
3 three           0
4 four            0
5 five            0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TXT                                                CHAR(15)

PDB1>select * from ilmtab;

ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ORA_ARCHIVE_STATE    YES NO
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)

-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
     and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;

update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible

-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2)

-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8

select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual;

STATE0   STATE1   STATE2   STATE3
-------- -------- -------- --------
0        1        1        1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;

select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making  hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
1 one             2
2 two             0
2 two             1
3 three           0
3 three           0
3 three           0
3 three           0
4 four            0
4 four            0
4 four            0
4 four            0
5 five            0
5 five            0
5 five            0
5 five            0

16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
Note : Had we created this table using sys, we could not have disabled row archiving .

-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:

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

----------------------------------------------------------------------------------------------------

Oracle 12c Index

----------------------------------------------------------------------------------------------