12C
Row Archival
User Rating: 
/ 1
- Details
-
Parent Category: Articles
-
Created on Monday, 10 October 2016 16:23
-
Last Updated on Monday, 10 October 2016 16:23
-
Published on Monday, 10 October 2016 16:23
-
Written by Guy Lambregts
-
Hits: 6027
In database archiving with Row Archival
SQL> connect I_LOVE_REDO/Echoes4YOU
Connected.
SQL> create table emp (empno number(7),fullname varchar2(40),job varchar2(9), MGR number(7)) ROW ARCHIVAL;
Table created.
When the table has the ROW ARCHIVAL attribute, there will be a pseudo column ora_archive_state, new rows will have the value 0 for ora_archive_state.
Rows with the ora_archive_state value of 0 will be considered ACTIVE rows.
Rows with any other value for ora_archive_state value than 0 will be considered ARCHIVED rows.
SQL> insert into emp values (1,'Guy Lambregts','DBA',null);
1 row created.
SQL> commit;
Commit complete.
SQL> select ora_archive_state from emp;
ORA_ARCHIVE_STATE
----------------------------------------------------------------------------------------------------
0
SQL> update emp set ora_archive_state = dbms_ilm.archivestatename(1) where empno = 1;
1 row updated.
SQL> insert into emp values (2,'Yorick De Smet','DBA',null);
1 row created.
SQL> commit;
Commit complete.
By default whilst we query the table we only see the ACTIVE rows.
SQL> select * from emp;
EMPNO FULLNAME JOB MGR
---------------- ---------------------------------------- --------- ----------------
2 Yorick De Smet DBA
We can see the archived rows when we switch the row archive visibility
SQL> alter session set row archival visibility = ALL;
Session altered.
SQL> select * from emp;
EMPNO FULLNAME JOB MGR
---------------- ---------------------------------------- --------- ----------------
1 Guy Lambregts DBA
2 Yorick De Smet DBA
SQL> alter session set row archival visibility = ACTIVE;
Session altered.
SQL> select * from emp;
EMPNO FULLNAME JOB MGR
---------------- ---------------------------------------- --------- ----------------
2 Yorick De Smet DBA
We can remove the ROW ARCHIVAL attribute at table level, doing so will remove the pseudo column
SQL> alter table emp NO ROW ARCHIVAL;
Table altered.
SQL> select * from emp;
EMPNO FULLNAME JOB MGR
---------------- ---------------------------------------- --------- ----------------
1 Guy Lambregts DBA
2 Yorick De Smet DBA
SQL> alter table emp ROW ARCHIVAL;
Table altered.
SQL> select * from emp;
EMPNO FULLNAME JOB MGR
---------------- ---------------------------------------- --------- ----------------
1 Guy Lambregts DBA
2 Yorick De Smet DBA
