In-Database Archiving

Companies may need to keep data for longer time for compliance purposes even there is not use these data for companies. In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.

Show all the columns in the table, including hidden columns:

SQL> SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,20) DATA_TYPE, COLUMN_ID AS COL_ID,
  SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH
  FROM USER_TAB_COLS WHERE TABLE_NAME='EMP';

NAME                   DATA_TYPE                          COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH
---------------------- ------------------------------ ---------- ---------- ---------- --- -----------
EMPLOYEE_VALID_DATE    NUMBER                                                        1 YES           0
ID                     NUMBER                                  1          1          2 NO            0
ENAME                  VARCHAR2                                2          2          3 NO           20
SURNAME                VARCHAR2                                3          3          4 NO           20
START_DATA             TIMESTAMP(6)                            4          4          5 NO            0
END_DATE               TIMESTAMP(6)                            5          5          6 NO            0

6 rows selected.

ROW ARCHIVAL option can be enabled during table creation using ROW ARCHIVEL key words or enable on exiting tables:

Enable Row Archival option during table creation:

SQL> CREATE TABLE POS.EMP1 (ID NUMBER,
ENAME VARCHAR2(20),
SURNAME VARCHAR2(20),
START_DATA TIMESTAMP,
END_DATE TIMESTAMP,
PERIOD FOR EMPLOYEE_VALID_DATE(START_DATA, END_DATE)) ROW ARCHIVAL;  

Table created.

Enable Row Archival option for existing table:

SQL> COLUMN ORA_ARCHIVE_STATE FORMAT a18;
SQL> SELECT ID, ORA_ARCHIVE_STATE FROM EMP;
SELECT ID, ORA_ARCHIVE_STATE FROM EMP
           *
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier


SQL> ALTER TABLE EMP ROW ARCHIVAL;

Table altered.

# ORA_ARCHIVE_STATE 0 means ROW ARCHIVAL option is enabled. 
SQL> SELECT ID, ORA_ARCHIVE_STATE FROM EMP;

        ID ORA_ARCHIVE_STATE
---------- ------------------
      1001 0
      1002 0
      1003 0
      1004 0
      1005 0

Row can be archived by updating with non-zero values for ORA_ARCHIVE_STATE or using DBMS_ILM.ARCHIVESTATENAME.

SQL> conn pos/oracle
Connected.

# Insert a value into ORA_ARCHIVE_STATE to set inactive. Except 0, all other values consider as inactive.

SQL> UPDATE EMP SET ORA_ARCHIVE_STATE = '1' WHERE ID = 1001;

1 row updated.

SQL> commit;

Commit complete.

SQL> SELECT * FROM EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> SELECT * FROM EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1001 Sam                  Samreen              01-01-2018      31-03-2019
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> SELECT * FROM EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1001 Sam                  Samreen              01-01-2018      31-03-2019
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SQL> SELECT * FROM EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020

Rows can be archived using DBMS_ILM.ARCHIVESTATENAME function as follows:

SQL> UPDATE EMP SET ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) WHERE ID = 1003;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020

Leave a Reply