Oracle Database 12c: In-Database Archiving (New feature)

oracle-12c-database-new-features

Hello everyone!

Oracle Database 12c have a new feature: In-Database Archiving. I will make a brief summary about this feature.

In-Archive Database: Gives the ability to keep both active operational data and non-active data in the same database tables.
This allow applications can access the data only in active state, archived data is kept on the same table.

Until now, archived data is not distinguishable from active data at SEGMENT level.

In-Archived Database:
Enable row-archival: ORA_ARCHIVE_STATE column added:

SQL>CREATE TABLE product
(ID NUMBER(7), PRODUCT_NAME VARCHAR2(40),
CLASS VARCHAR2(9), BATCH_N NUMBER(7))
ROW ARCHIVAL;

To set the value of actveness of row data ROW ARCHIVAL needs to be enable at the segment level.

New rows: ORA_ACHIVE_STATE value 0
Update ORA_ARCHIVE_STATE value 1 for now archiving.

  • Now distincts states for a row in a table can be defined: acitve, non-active.
    This add a invisible column ORA_ARCHIVE_STATE that will contain the state of each row.
    By default, the rows inserted is active and the ORA_ARCHIVE_STATE value is ‘0’.

View ORA_ARCHIVE_STATE column:
SQL> SELECT ORA_ARCHIVE_STATE, product_name FROM product;

ORA_ARCHIVE_STATE  PRODUCT_NAME
———————–    ————-
                            0        LEMON
                           1         JUICE
                           1         BEAN
                           1         MEAT

  • After a certain elapsed time, the row can be less frequently accessed and rarely updated, but
    still considered as active. Then the row will be rarely accessed and no more updated, it is
    retained for records retention and/or compliance purposes: it is considered in a non-active
    state.

Setting rows to back in active state:

SQL> UPDATE product SET ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(0);

The ORA_ARCHIVE_STATE it is visible only when explicitly specified select.

Session visibility control

SQL> select ORA_ARCHIVE_STATE, product_name from product;

ORA_ARCHIVE_STATE FULLNAME
—————– ——————
                    0 LEMON            

Enable application visibility of all rows:

SQL> alter session set ROW ARCHIVAL VISIBILITY = ALL;
Session altered

SQL> SELECT ORA_ARCHIVE_STATE, product_name FROM product;

ORA_ARCHIVE_STATE PRODUCT_NAME
—————- ————-
                     0   LEMON
                     1   JUICE
                     1   BEAN
                     1   MEAT          

  • The session have visibility only for active rows
  • With the statement ALTER SESSION extended with a new ROW ARCHIVAL VISIBILITY
    clause. By default, the value is ALL ACTIVE value allows viewing only active data, and not
    non-active data, in the session.

Disable Row-Archival

Disable row-archival: ORA_ARCHIVE_STATE column removed:

SQL> ALTER TABLE product NO ROW ARCHIVAL;

The insible column ORA_ARCHIVE_STATE is dropped.
And no data distinguish is made (active, non-active rows).

I hope you enjoy!

Cheers

Felipe!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s