Very often and for several diverse reasons, information should be considered as “inactive” instead of “deleted.” One of the new functionalities introduced in 12c release is the “In-Database Archiving” feature, allowing this type of functionality as a standard component of Oracle Database Information Lifecycle Management (ILM) strategy. Using this new feature you can store “deleted rows” online (inactive information) for a long time without compromising application performance.
In-Database Archiving enables you to mark rows as “invisible” to your application. These rows can be compressed and optimized in order to save resources and operation times. Also, the data contained in these rows is available for future use in case of need. You can switch the state from being “inactive” to be “active” at any given moment.
In order to use this feature, just specify the “ROW ARCHIVAL” clause during the CREATE TABLE statement.
When this clause is specified, the given table is created containing an extra hidden column named ORA_ARCHIVE_STATE that indicates if the row is “visible” (available) or not.
Possible values for this column are
- 0 = ACTIVE (default)
- 1 = ARCHIVED
Using In-Database Archiving
First, create a sample user and a table to be used as sample. Add the “ROW ARCHIVAL” clause during creation process:
SQL> CREATE USER z_test 2 IDENTIFIED BY welcome1; User created. SQL> GRANT CONNECT, RESOURCE TO z_test; Grant succeeded. SQL> ALTER USER z_test QUOTA UNLIMITED ON users; User altered. SQL> CONN z_test/welcome1 Connected. SQL> CREATE TABLE cars_for_sale ( 2 units NUMBER, 3 make VARCHAR2(20), 4 model VARCHAR2(30), 5 year NUMBER) 6 ROW ARCHIVAL; Table created.
Describe the table in order to verify the expected behavior:
SQL> DESC cars_for_sale Name Null? Type ----------------------------------------- -------- ---------------------------- UNITS