Hide row data from table without delete with In-Database Archiving new feature in Oracle 12c
In-Database Archiving feature helps application to hide row data of table without delete the actual data.
In Application, For managing we add one extra column in the table which will tell application that data is deleted or visible state. Suppose make a column and add entry as 1 or 0. 1 for visible through application and 0 for not visible. Then application team used where clause to check the visibility during operations in every SQL Query.
With help of in-database archiving, we can use this with existing application without much change in application side. In-Database archiving is also using same concept, it enable extra hidden column “ora_archive_state”.
Enable and disable the hidden row feature “In-Database Archive” at table level
-- Enable the archival process with table creation
CREATE TABLE test1(id NUMBER,name VARCHAR2(50)) ROW ARCHIVAL;
-- Enable the archival process
ALTER TABLE test1 ROW ARCHIVAL;
-- Disable the archival process
ALTER TABLE test1 NO ROW ARCHIVAL;
Check the feature in enabled or disabled
Note: If you see the ORA_ARCHIVE_STATE row then enabled.
-- If enabled
col column_name for a18
col data_type for a15
SELECT column_id,column_name,data_type,hidden_column FROM user_tab_cols
WHERE table_name = 'TEST1' ORDER BY column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE HID
---------- ------------------ --------------- ---
1 ID NUMBER NO
2 NAME VARCHAR2 NO
ORA_ARCHIVE_STATE VARCHAR2 YES
---- IF Disabled.
SELECT column_id,column_name,data_type,hidden_column FROM user_tab_cols
WHERE table_name = 'TEST1' ORDER BY column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE HID ---------- --------------- --------------- --- 1 ID NUMBER NO 2 NAME VARCHAR2 NO
Example of using In-Database Archiving
--Check all rows of table
SQL> SELECT COUNT(*) FROM test1;
COUNT(*)
----------
1000
-- Check the table data which is enabled to see by application or user
SQL> SELECT ora_archive_state, COUNT(*) FROM test1 GROUP BY ora_archive_state;
O COUNT(*) - ---------- 0 1000
--Update the rows to make it invisible by updating ora_archive_state column value to 1.
update test1 set ora_archive_state = 1 where rownum <= 500;
--Check the rows visible after making row invisible.
SQL> SELECT COUNT(*) FROM test1;
COUNT(*)
----------
500
You can check the visible or invisible rows by setting parameter at session level
-- MAke row visible at session level
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL> SELECT COUNT(*) FROM test1;
COUNT(*)
----------
1000
--- Make row invisible at session level
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
Session altered.
SQL> SELECT COUNT(*) FROM test1;
COUNT(*)
----------
500
