This week I followed the Oracle 12c New Features course in the Netherlands by Harald van Breederode. In this course the new row archiving feature was mentioned. Very interesting feature, but if you want to look up which table has row archiving enabled, it seems that the only thing you can do is to look up all tables with an ORA_ARCHIVE_STATE column present.
That seems odd to me, because the feature is a property of the table, so CDB/DBA_TABLES should have a column indicating the feature. The following PL/SQL block, executed as sys in a 12.1.0.1.0 mirrors the investigation I did.
declare /* Create a table in another schema. When you don't, the alter table will give an ORA-12988: cannot drop column from table owned by SYS. and we are dropping the hidden column ORA_ARCHIVE_STATE from our new table when we alter the row archival output: Version: 12.1.0.1.0 Before : 140755205095424 Bits unset: 29 34 47 After : 17716740096 Bits unset: 29 34 EDWIN no archival EDWIN_BENCHMARK with archival */ cursor c_prop is select t.property from tab$ t , obj$ o where o.obj# = t.obj# and o.name = 'EDWIN' ; l_after tab$.property%type; l_before tab$.property%type; l_version v$instance.version%type; -- function BITS_UNSET ( p_n number ) return varchar2 is l_power number; l_return varchar2(4000); begin for l_i in 0..126 loop l_power := power(2,l_i); -- if ( l_power > p_n ) then exit; end if; -- if bitand(p_n,l_power) != 0 then l_return := l_return||' '||l_i; end if; end loop; return trim(l_return); end; begin select version into l_version from v$instance; dbms_output.put_line('Version: '||l_version); -- begin execute immediate 'drop table scott.edwin'; exception when others then null; end; begin execute immediate 'drop table scott.edwin_benchmark'; exception when others then null; end; -- execute immediate 'create table scott.edwin_benchmark (kol number) row archival'; execute immediate 'create table scott.edwin (kol number) row archival'; -- open c_prop; fetch c_prop into l_before; close c_prop; dbms_output.put_line('Before : '||l_before||' Bits unset: '||BITS_UNSET(l_before)); -- execute immediate 'alter table scott.edwin no row archival'; -- open c_prop; fetch c_prop into l_after; close c_prop; dbms_output.put_line('After : '||l_after||' Bits unset: '||BITS_UNSET(l_after)); -- for r_check in ( select o.name || decode( bitand(t.property, power(2,47)) , 0, ' no archival' , ' with archival' ) as text from tab$ t , obj$ o where o.obj# = t.obj# and o.name in ('EDWIN','EDWIN_BENCHMARK') ) loop dbms_output.put_line(r_check.text); end loop; end; /
So, the flag is bit 47 in the property column of tab$.
I guess we can wait for a future upgrade of 12c where they unlock this information. In the mean time, you can use the information above for a custom view….or just look for the existence of ORA_ARCHIVE_STATE.