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
