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
----------------------------------------------------------------------------------------------