19 | 03 | 2024
Latest Articles
Popular Articles

12C

Row Archival

User Rating:  / 1
PoorBest 

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