12C: IN DATABASE ARCHIVING

****************************************************************

           Oracle Database Archiving Testing

****************************************************************

The archiving feature relate to enable archiving rows within a table instead of deleting for unnecessary records. The archived rows are remained in the original table but marked as invisible to running applications for future use.

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.

Implementation :

Parameter: row archival

Hidden Column on Table: ORA_ARCHIVE_STATE

1. Create a tablespace and user for testing

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

SQL> create tablespace test_arch datafile '/BPELAIT2/MONDB01/MONDB01/test_arch01.dbf' size 20M;

Tablespace created.

SQL> grant connect, resource, dba to monowar identified by password;

Grant succeeded.

SQL> alter user monowar default tablespace test_arch;

User altered.

2. Create a table with "row archival"

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

SQL> connect monowar/******

Connected.

SQL> create table test_arch

   (idno number(7),

    name varchar2(60),

    Address varchar2(20))

    ROW archival;

 

Table created.

SQL> desc test_arch

 Name                                      Null?    Type

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

 IDNO                                               NUMBER(7)

 NAME                                               VARCHAR2(60)

 ADDRESS                                            VARCHAR2(20)

3. Verify Archival Column

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

i. Insert data

SQL> insert into test_arch (IDNO,NAME,ADDRESS)

    values (100,'SUMMER','RAJSHAHI'); 

1 row created.

SQL> insert into test_arch (IDNO,NAME,ADDRESS)

    values (200,'WINTER','DHAKA');

 

1 row created.

SQL> insert into test_arch (IDNO,NAME,ADDRESS)

    values (220,'WINTER','KHULNA'); 

1 row created.

SQL> insert into test_arch (IDNO,NAME,ADDRESS)

    values (300,'AUTUMN','CHITTA'); 

1 row created.

SQL> commit;

Commit complete.

ii. Check Status

SQL> select ora_archive_state, name from test_arch;

ORA_ARCHIVE_STATE       NAME

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

0                       SUMMER

0                       WINTER

0                       WINTER

0                       AUTUMN

Note: A new row_archival column is displayed and with default value 0 (for active rows).

4. Make some update to change it to inactove

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

Update ORA_ARCHIVE_STATE column to a non zero value to make selected rows inactive

SQL> update test_arch set ORA_ARCHIVE_STATE=3 where IDNO=200;

1 row updated.

verify the status (Displays only active rows)

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

SQL> select ora_archive_state, name from test_arch;

ORA_ARCHIVE_STATE       NAME

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

0                       SUMMER

0                       WINTER

0                       AUTUMN

SQL> commit;

5. Query to get the active and non-active rows data

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

SQL> alter session set row archival visibility=all;

Session altered.

SQL> select ora_archive_state, name from test_arch;

ORA_ARCHIVE_STATE             NAME

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

0                       SUMMER

3                       WINTER

0                       WINTER

0                       AUTUMN

Commit complete.

Make it inactive again

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

SQL> alter session set row archival visibility=active;

Session altered.

SQL> select ora_archive_state, name from test_arch;

ORA_ARCHIVE_STATE       NAME

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

0                       SUMMER

0                       WINTER

0                       AUTUMN

6. Test : CTAS (create table as select) based on row-archival table

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

SQL> create table cp_test_arch as select * from test_arch;

Table created.

SQL> select ora_archive_state, name from cp_test_arch;

select ora_archive_state, name from cp_test_arch

       *

ERROR at line 1:

ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

NOTE: row-archival enabled table does not propagate the row-archival state to the new table.

7. Now set row archival for table - cp_test_arch

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

SQL> alter table cp_test_arch row archival;

Table altered.

select ora_archive_state, name from cp_test_arch;

ORA_ARCHIVE_STATE             NAME

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

0                       SUMMER

0                       WINTER

0                       WINTER

0                       AUTUMN

SQL> insert into cp_test_arch

  2  select IDNO+33,name, ADDRESS from test_arch;

3 rows created.

Note: One row which is inactive did not come through.

SQL> select * from cp_test_arch;

      IDNO NAME         ADDRESS

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

       100 SUMMER     RAJSHAHI

       200 WINTER     DHAKA

       220 WINTER     KHULNA

       300 AUTUMN     CHITTA

       133 SUMMER     RAJSHAHI

       253 WINTER     KHULNA

       333 AUTUMN     CHITTA

7 rows selected.

SQL> rollback;

Rollback complete.

SQL> select ora_archive_state, name from cp_test_arch;

ORA_ARCHIVE_STATE             NAME

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

0                       SUMMER

0                       WINTER

0                       WINTER

0                       AUTUMN

8. Change the archival mode and insert data from row archival table

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

SQL> alter session set row archival visibility=all;

Session altered.

SQL> insert into cp_test_arch

  2  select IDNO+33,name, ADDRESS from test_arch;

4 rows created.

So all active and inactive rows are inserted.

SQL> select ora_archive_state, name from cp_test_arch;

ORA_ARCHIVE_STATE       NAME

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

0     SUMMER

0     WINTER

0     WINTER

0     AUTUMN

0     SUMMER

0     WINTER

0     WINTER

0     AUTUMN

8 rows selected.

Note: Insert Statement set the dafault row-archival state.

9. DISABLE the row archival

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

SQL> alter table test_arch no row archival;

Table altered.

SQL> c/test_arch/cp_test_arch

  1* alter table cp_test_arch no row archival

SQL> /

Table altered.

SQL> select ora_archive_state, name from test_arch;

select ora_archive_state, name from test_arch

       *

ERROR at line 1:

ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

SQL>  c/test_arch/cp_test_arch

  1* select ora_archive_state, name from cp_test_arch

SQL> /

select ora_archive_state, name from cp_test_arch

       *

ERROR at line 1:

ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

======================================================================