Which two statements are true when row archival management is enabled?
A.
The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY
session parameter.
B.
The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference
activity tracking columns, to indicate that a row is no longer considered active.
C.
The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.
D.
The ORA_ARCHIVE_STATE column is visible if referenced in the select list of a query.
E.
The ORA_ARCHIVE_STATE column is updated automatically by the Oracle Server based on
activity tracking columns, to Indicate that a row is no longer considered active.
Explanation:
A: Below we see a case where we set the row archival visibility parameter to “all”
thereby allowing us to see all of the rows that have been logically deleted:
alter session set row archival visibility = all;
We can then turn-on row invisibility back on by changing row archival visibility = “active”:
alter session set row archival visibility = all;
B: To use ora_archive_state as an alternative to deleting rows, you need the following settings and
parameters:
1. Create the table with the row archival clause
create table mytab (col1 number, col2 char(200)) row archival;
2. Now that the table is marked as row archival, you have two methods for removing rows, a
permanent solution with the standard delete DML, plus the new syntax where you set
ora_archive_state to a non-zero value:
update mytab set ora_archive_state=2 where col2=’FRED’;
3. To make “invisible rows” visible again, you simply set the rows ora_archive_state to zero:
update mytab set ora_archive_state=0 where col2=’FRED’;
Note:
* Starting in Oracle 12c, Oracle provides a new feature that allow you to “logically delete” a row in
a table without physically removing the row. This effectively makes deleted rows “invisible” to all
SQL and DML, but they can be revealed at any time, providing a sort of “instant” rollback method.
To use ora_archive_state as an alternative to deleting rows.
Maybe someone out there can confirm this but I think C,D is correct. Although B sounds correct, too.
B C D
SQL> alter table sales row archival;
Table altered.
SQL> select distinct ora_archive_state from class;
ORA_ARCHIVE_STATE
——————————————————————————–
0
Note – the column ORA_ARCHIVE_STATE is now added to the table class and is a hidden column.
SQL> update class
2 set ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1)
3 where age select distinct ora_archive_state from class;
ORA_ARCHIVE_STATE
——————————————————————————–
0
SQL> select count(*) from sales;
COUNT(*)
———-
10
SQL> alter session set row archival visibility=ALL;
Session altered.
SQL> select count(*) from sales;
COUNT(*)
———-
25
C and D
A D
A = Set the archival visibility to ACTIVE for the session. This will allow this session to view only active (unarchived) rows.
alter session set row archival visibility = active;
D = select employee_id, first_name, ora_archive_state from emp_arch;