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.
For me, CD
SQL> alter table sales row archival;
Table altered.
SQL> select distinct ora_archive_state from sales;
ORA_ARCHIVE_STATE
——————————————————————————–
0
I thing C,D – TRUE
http://gavinsoorma.com/2013/08/oracle-12c-new-feature-in-database-archiving/
CD
C and D appears to be correct
yes, C,D
http://docs.oracle.com/database/121/VLDBG/to_vldbg14154_d436.htm#VLDBG14154
Why not B ?
You must take care of updating column ORA_ARCHIVE_STATE. It is not done automaticaly.
So the answer is BCD
BCD
Only E is not TRUE
Also A is not TRUE, the ROW ARCHIVAL VISIBILITY session parameter controls the visibility of the rows, not the column ORA_ARCHIVE_STATE
AB are correct.
http://docs.oracle.com/database/121/VLDBG/GUID-5A76B6CE-C96D-49EE-9A89-0A2CB993A933.htm#VLDBG14154
I think every single answer is TRUE except E, although you could argue it CAN be true if you set up certain PL/SQL to accomplish it.
Look at
http://gavinsoorma.com/2013/08/oracle-12c-new-feature-in-database-archiving/
and you’ll see working examples of A,B,C and D.
Also, check out
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/row_archival/row_archival.html
BCD
The ALTER SESSION statement is extended with a new ROW ARCHIVAL VISIBILITY clause. By default, the value is ALL. The ACTIVE value allows viewing only active data, and not non-active data, in the session.
So, the default setting for ROW ARCHIVAL VISIBILTY is ALL and not ACTIVE. C is wrong.
BD
by default is active, just tested
BCD
http://www.orafaq.com/node/2824
C
-> In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplished by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed, by setting a session parameter ROW ARCHIVAL VISIBILITY. — To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY = all at session level
D
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
———- ————— ——————–
1 one 0
2 two 0
3 three 0
4 four 0
5 five 0
— Note that this column is not visible when we describe the table or simply issue select * from …
BCD is correct, but BD is probably the correct answer for the test.
Here is a snippet from the student guide:
“The ALTER SESSION statement is extended with a new ROW ARCHIVAL VISIBILITY clause. By default, the value is ALL. The ACTIVE value allows viewing only active data, and not non-active data, in the session.”
Of course, this is incorrect. So I wonder if the test writers believe that C is false.
A – correct:
Alter session set row archival state = ALL or ACTIVE to control the visibility of archived rows
B – Correct;
State column must be updated manually . when inserted – it is 0 (means ACTIVE). To update this column use DBMS_ILM.ARCHIVESTATENAME(1) or you can pass 0. Any value other than 0 is non-active data.
C – Wrong:
Default is ALL. See “Steve” message above.
D – Correct
Yes, it can be referenced in the select list similar to ROWNUM, ROWID
E- Wrong. See B.
ANS : ABD
C and D
C is correct, the default is active
From the Oracle 12c docs
ROW ARCHIVAL VISIBILITY
Use this clause to configure row archival visibility for the session. This clause lets you implement In-Database Archiving, which allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table.
If you specify ACTIVE, then the database will consider only active rows when performing queries on tables that are enabled for row archival. This is the default.
If you specify ALL, then the database will consider all rows when performing queries on tables that are enabled for row archival.
D is correct because the ora_archive_state is an invisible column, and it is only visible when explicitly specified in select list of queries
A is wrong because the column is always invisible, row archival visibility control the rows , not the column
B and D are wrong because it is update manually only
CD
please Do you have valid dump for the exam ?
B,D
CD is correct, but B seems to be correct too…
CD
—
A is wrong
The ROW ARCHIVAL VISIBILITY session parameter controls the visibility of the rows, not the column ORA_ARCHIVE_STATE
BE are wrong.
Activity tracking columns is part of the Temporal Validity feature, it doesnt need the ORA_ARCHIVE_STATE column