Some strange behaviors I need to get the concept straighten up .
Setup
--Create dummy table
create table ROW_ARC_TEST(
ID NUMBER(2),
NAME1 VARCHAR2(10),
NAME2 VARCHAR2(10)
) ROW ARCHIVAL
;
-- Desc the table
SQL> desc row_arc_test;
Name Null? Type
--------------------------------
ID NUMBER(2)
NAME1 VARCHAR2(10)
NAME2 VARCHAR2(10)
Name Null? Type
--------------------------------
ID NUMBER(2)
NAME1 VARCHAR2(10)
NAME2 VARCHAR2(10)
--Insert 5 rows
SQL> insert into row_arc_test values (1,'ONEA','ONEB');
insert into row_arc_test values (2,'TWOA','TWOB');
insert into row_arc_test values (3,'THREEA','THREEB');
insert into row_arc_test values (4,'FOURA','FOURB');
insert into row_arc_test values (5,'FIVEA','FIVEB');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
insert into row_arc_test values (2,'TWOA','TWOB');
insert into row_arc_test values (3,'THREEA','THREEB');
insert into row_arc_test values (4,'FOURA','FOURB');
insert into row_arc_test values (5,'FIVEA','FIVEB');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
commit;
--select the table
SQL> select * from row_arc_test;
ID NAME1 NAME2
---------- ---------- ----------
1 ONEA ONEB
2 TWOA TWOB
3 THREEA THREEB
4 FOURA FOURB
5 FIVEA FIVEB
Game on
-- 0 means archival in ON.
SQL> col ora_archive_state format a5;
SQL> select ora_archive_state, name1, name2 from row_arc_test;
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 THREEA THREEB
0 FOURA FOURB
0 FIVEA FIVEB
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 THREEA THREEB
0 FOURA FOURB
0 FIVEA FIVEB
masking out column THREEA.
SQL> update row_arc_test set ora_archive_state = 1 where name1='THREEA';
1 row updated.
1 row updated.
SQL> commit;
SQL> col ora_archive_state format a5;
SQL> select ora_archive_state, name1, name2 from row_arc_test;
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 FOURA FOURB
0 FIVEA FIVEB
SQL> col ora_archive_state format a5;
SQL> select ora_archive_state, name1, name2 from row_arc_test;
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 FOURA FOURB
0 FIVEA FIVEB
Note: strange behavior, enablement of archived row not showing!
Updating the archived row.
SQL> update row_arc_test set name1='THREE_C' where name1='THREEA';
0 rows updated.
SQL> update row_arc_test set name1='THREE_C' where name1='THREEA';
0 rows updated.
SQL> update row_arc_test set name2='THREE_SEEME' where name2='THREEB';
0 rows updated.
SQL>commit;
0 rows updated.
SQL> update row_arc_test set name1='THREE_C' where name1='THREEA';
0 rows updated.
SQL> update row_arc_test set name2='THREE_SEEME' where name2='THREEB';
0 rows updated.
SQL>commit;
Note 1: Nothing can be updated for that archived row!
Note 2: even the THREE_SEEME is over the column definition, it still take it as it is instead of error out. Brilliant.
--Showing row archival enablement. This explained why earlier archived row not showing.
Turn archive visibility all visible.
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL> select * from row_arc_test;
ID NAME1 NAME2
---------- ---------- ----------
1 ONEA ONEB
2 TWOA TWOB
3 THREEA THREEB
4 FOURA FOURB
5 FIVEA FIVEB
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
1 3 THREEA THREEB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
Session altered.
SQL> select * from row_arc_test;
ID NAME1 NAME2
---------- ---------- ----------
1 ONEA ONEB
2 TWOA TWOB
3 THREEA THREEB
4 FOURA FOURB
5 FIVEA FIVEB
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
1 3 THREEA THREEB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
Note: bad choice of wording .. ALL vs Active .. both sounds like turing archival row ON !
Turning archival row ON
SQL> alter session set row archival visibility = active;
Session altered.
SQL> desc row_arc_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME1 VARCHAR2(10)
NAME2 VARCHAR2(10)
SQL> select * from row_arc_test;
ID NAME1 NAME2
---------- ---------- ----------
1 ONEA ONEB
2 TWOA TWOB
4 FOURA FOURB
5 FIVEA FIVEB
SQL> select ora_archive_state, name1, name2 from row_arc_test;
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 FOURA FOURB
0 FIVEA FIVEB
Session altered.
SQL> desc row_arc_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME1 VARCHAR2(10)
NAME2 VARCHAR2(10)
SQL> select * from row_arc_test;
ID NAME1 NAME2
---------- ---------- ----------
1 ONEA ONEB
2 TWOA TWOB
4 FOURA FOURB
5 FIVEA FIVEB
SQL> select ora_archive_state, name1, name2 from row_arc_test;
ORA_A NAME1 NAME2
----- ---------- ----------
0 ONEA ONEB
0 TWOA TWOB
0 FOURA FOURB
0 FIVEA FIVEB
SQL> COL TABLE_NAME FORMAT A12;
SQL> COL COLUMN_NAME FORMAT A25;
SQL> SELECT table_name, column_name, hidden_column, user_generated from dba_tab_cols where table_name='ROW_ARC_TEST';
TABLE_NAME COLUMN_NAME HID USE
------------ ------------------------- --- ---
ROW_ARC_TEST NAME2 NO YES
ROW_ARC_TEST NAME1 NO YES
ROW_ARC_TEST ID NO YES
ROW_ARC_TEST ORA_ARCHIVE_STATE YES NO
SQL>
The effect on delete and truncate when ROW ARCHIVAL where it is set to ACTIVE or ALL;
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
1 3 THREEA THREEB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> delete from row_arc_test;
5 rows deleted.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
no rows selected
SQL> rollback;
Rollback complete.
SQL> alter session set row archival visibility = active;
Session altered.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> delete from row_arc_test;
4 rows deleted.
SQL> rollback;
Rollback complete.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> truncate table row_arc_test;
Table truncated.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
no rows selected
SQL>
Session altered.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
1 3 THREEA THREEB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> delete from row_arc_test;
5 rows deleted.
Note: As long as Oracle sees them, it will be deleted.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
no rows selected
SQL> rollback;
Rollback complete.
SQL> alter session set row archival visibility = active;
Session altered.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> delete from row_arc_test;
4 rows deleted.
Note: Deleting on a ROW ARCHIVAL VISIBILITY when it is ACTIVE, it will not delete the rows that are activated.
SQL> rollback;
Rollback complete.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
ORA_A ID NAME1 NAME2
----- ---------- ---------- ----------
0 1 ONEA ONEB
0 2 TWOA TWOB
0 4 FOURA FOURB
0 5 FIVEA FIVEB
SQL> truncate table row_arc_test;
Table truncated.
SQL> select ora_archive_state, id, name1, name2 from row_arc_test;
no rows selected
SQL>
Note: Truncating will get rid of everything regardless which the ROW ARCHIVAL setting is.
Oracle DBA should really get familiar with this especially going to use ROW ARCHIVAL along with non-static data which means, data will be updated or deleted throughout the time. I can imagine, this can totally jacked up the data if DBA putting the wrong rows on VISIBLE and ACTIVE or vice versa.
No comments:
Post a Comment