In-Database Archiving

http://docs.oracle.com/database/121/VLDBG/GUID-5A76B6CE-C96D-49EE-9A89-0A2CB993A933.htm#VLDBG14154
Tested on:
Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
1.) Create Table:
create sequence cmagno.seq_IDBARCH 
minvalue 1 maxvalue 9999999999 nocache order increment by 1;
CREATE TABLE cmagno.test_IDBARCH 
(  id_test          NUMBER,
 text VARCHAR2(100),
 dt date,
 CONSTRAINT test_IDBARCH PRIMARY KEY (id_test)
 )
 ROW ARCHIVAL;
declare
begin
for i in 1..100 loop
 insert into cmagno.test_IDBARCH 
 values 
(cmagno.seq_IDBARCH.nextval,'Test: ' || to_char(i), sysdate);
 end loop;
 commit;
 end;
 /
SQL> desc cmagno.test_IDBARCH
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ID_TEST                                   NOT NULL NUMBER
 TEXT                                               VARCHAR2(100)
 DT                                                 DATE
SQL> select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 100
col column_name FORMAT A20
 col data_type FORMAT A20
 col table_name format a20
 col DATA_TYPE_OWNER format a20
SELECT
 OWNER,
 TABLE_NAME,
 COLUMN_NAME,
 HIDDEN_COLUMN,
 DATA_TYPE,
 DATA_TYPE_MOD,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 NULLABLE
 FROM   dba_tab_cols
 WHERE  table_name = 'TEST_IDBARCH'
 ORDER BY column_id;
OWNER           TABLE_NAME           COLUMN_NAME          HID DATA_TYPE                 DATA_LENGTH DATA_PRECISION DATA_SCALE N
 --------------- -------------------- -------------------- --- -------------------- --- -------------------- ----------- -------------- ---------- -
 CMAGNO          TEST_IDBARCH         ID_TEST              NO  NUMBER                                         22                           N
 CMAGNO          TEST_IDBARCH         TEXT                 NO  VARCHAR2                                      100                           Y
 CMAGNO          TEST_IDBARCH         DT                   NO  DATE                                            7                           Y
 CMAGNO          TEST_IDBARCH         ORA_ARCHIVE_STATE    YES VARCHAR2                                     4000                           Y
select distinct ORA_ARCHIVE_STATE from cmagno.TEST_IDBARCH;
ORA_ARCHIVE_STATE
 --------------------
 0
1 row selected.
2.) Putting rows on invisible state:
update cmagno.TEST_IDBARCH
 set ORA_ARCHIVE_STATE=1
 where ID_TEST <51;
 commit;
select count(*), ORA_ARCHIVE_STATE
 from cmagno.TEST_IDBARCH
 group by ORA_ARCHIVE_STATE;
COUNT(*) ORA_ARCHIVE_STATE
 ---------- ------------------
 50 0
select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 50
3.) Showing Rows
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 100
1 row selected.
select count(*), ORA_ARCHIVE_STATE
 from cmagno.TEST_IDBARCH
 group by ORA_ARCHIVE_STATE;
COUNT(*) ORA_ARCHIVE_STATE
 ---------- --------------------
 50 1
 50 0
2 rows selected.
To put rows back in the INVISIBLE state:
 ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

					
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s