Posted by In 12c, we introduced a feature call Row Archival, which is a means of integrating typical archival models that we used to build with “home grown” solutions into the database. In effect, you can mark a set of rows in a table as “archived” and even though they are retained in the table, they are (by default) no longer visible to queries. I won’t go through the feature in full detail because many others have already done do.
However, one small idiosyncrasy of using row archival is that it is not immediately apparent if a table has been marked as such. For example, if I create a simple table and add the row archival facility.
SQL> create table t ( x int );
Table created.
SQL> alter table t row archival;
Table altered.
Then looking at USER_TABLES there really isn’t anything in there that gives an indicating that this table has row archival enabled.
SQL> select * from user_tables
2 where table_name = 'T'
3 @pr
==============================
TABLE_NAME : T
TABLESPACE_NAME : USERS
CLUSTER_NAME :
IOT_NAME :
STATUS : VALID
PCT_FREE : 10
PCT_USED :
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT :
NEXT_EXTENT :
MIN_EXTENTS :
MAX_EXTENTS :
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
LOGGING : YES
BACKED_UP : N
NUM_ROWS :
BLOCKS :
EMPTY_BLOCKS :
AVG_SPACE :
CHAIN_CNT :
AVG_ROW_LEN :
AVG_SPACE_FREELIST_BLOCKS :
NUM_FREELIST_BLOCKS :
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE :
LAST_ANALYZED :
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : NO
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : YES
CLUSTER_OWNER :
DEPENDENCIES : DISABLED
COMPRESSION : DISABLED
COMPRESS_FOR :
DROPPED : NO
READ_ONLY : NO
SEGMENT_CREATED : NO
RESULT_CACHE : DEFAULT
CLUSTERING : NO
ACTIVITY_TRACKING :
DML_TIMESTAMP :
HAS_IDENTITY : NO
CONTAINER_DATA : NO
INMEMORY : DISABLED
INMEMORY_PRIORITY :
INMEMORY_DISTRIBUTE :
INMEMORY_COMPRESSION :
INMEMORY_DUPLICATE :
DEFAULT_COLLATION : USING_NLS_COMP
DUPLICATED : N
SHARDED : N
EXTERNAL : NO
HYBRID : NO
CELLMEMORY :
CONTAINERS_DEFAULT : NO
CONTAINER_MAP : NO
EXTENDED_DATA_LINK : NO
EXTENDED_DATA_LINK_MAP : NO
INMEMORY_SERVICE :
INMEMORY_SERVICE_NAME :
CONTAINER_MAP_OBJECT : NO
MEMOPTIMIZE_READ : DISABLED
MEMOPTIMIZE_WRITE : DISABLED
HAS_SENSITIVE_COLUMN : NO
ADMIT_NULL : NO
DATA_LINK_DML_ENABLED : NO
LOGICAL_REPLICATION : ENABLED
PL/SQL procedure successfully completed.
One option I suppose to detect if a table has row archival is to try add it again, ie
SQL> alter table t row archival;
alter table t row archival
*
ERROR at line 1:
ORA-38396: table is already enabled for the ILM feature
but of course if the table did not have row archival enabled, then now it has and you need to remember to drop it.
Currently, I think the only way you can detect row archival would be to look for the existence of a column called ORA_ARCHIVE_STATE on the table. This is a hidden column so you need to query USER_TAB_COLS not USER_TAB_COLUMNS.
SQL> select column_name, hidden_column
2 from user_tab_cols
3 where table_name = 'T';
COLUMN_NAME HID
------------------------------ ---
X NO
SYS_NC00002$ YES
ORA_ARCHIVE_STATE YES
If you’re into a slightly more geeky solution (and you have SELECT ANY DICTIONARY access) we can dive into the core dictionary tables to see what happens when I add row archival for a table.
Here is the data in SYS.TAB$ for my table T before I added row archival.
SQL> select * from sys.tab$ where obj# = 6565325
2 @pr
==============================
OBJ# : 6565325
DATAOBJ# : 6565325
TS# : 5
FILE# : 0
BLOCK# : 0
BOBJ# :
TAB# :
COLS : 1
CLUCOLS :
PCTFREE$ : 10
PCTUSED$ : 40
INITRANS : 1
MAXTRANS : 255
FLAGS : 1073741825
AUDIT$ : -----------------------------
ROWCNT :
BLKCNT :
EMPCNT :
AVGSPC :
CHNCNT :
AVGRLN :
AVGSPC_FLB :
FLBCNT :
ANALYZETIME :
SAMPLESIZE :
DEGREE :
INSTANCES :
INTCOLS : 1
KERNELCOLS : 1
PROPERTY : 17716740096
TRIGFLAG : 0
SPARE1 : 736
SPARE2 :
SPARE3 :
SPARE4 :
SPARE5 :
SPARE6 : 22-MAY-22
SPARE7 :
SPARE8 :
SPARE9 :
SPARE10 :
ACDRFLAGS :
ACDRTSOBJ# :
ACDRDEFAULTTIME :
ACDRROWTSINTCOL# : 0
And here is the data in SYS.TAB$ for my table T after I added row archival.
SQL> select * from sys.tab$ where obj# = 6565325
2 @pr
==============================
OBJ# : 6565325
DATAOBJ# : 6565325
TS# : 5
FILE# : 0
BLOCK# : 0
BOBJ# :
TAB# :
COLS : 1
CLUCOLS :
PCTFREE$ : 10
PCTUSED$ : 40
INITRANS : 1
MAXTRANS : 255
FLAGS : 1073741825
AUDIT$ : -----------------------------
ROWCNT :
BLKCNT :
EMPCNT :
AVGSPC :
CHNCNT :
AVGRLN :
AVGSPC_FLB :
FLBCNT :
ANALYZETIME :
SAMPLESIZE :
DEGREE :
INSTANCES :
INTCOLS : 1
KERNELCOLS : 1
PROPERTY : 140755205095424
TRIGFLAG : 0
SPARE1 : 736
SPARE2 :
SPARE3 :
SPARE4 :
SPARE5 :
SPARE6 : 22-MAY-22
SPARE7 :
SPARE8 :
SPARE9 :
SPARE10 :
ACDRFLAGS :
ACDRTSOBJ# :
ACDRDEFAULTTIME :
ACDRROWTSINTCOL# : 0
Everything looks the same except for the value in the PROPERTY column. If we look at the difference between those two values:
SQL> select 140755205095424-17716740096 x from dual;
X
---------------------------
140737488355328
then this number does not appear particular special unless you spend you weekends studying the power of 2 😆
SQL> select log(2,140755205095424-17716740096) from dual;
LOG(2,140755205095424-17716740096)
----------------------------------
47
So it looks like row archival for a table is indicated by the 48th bit in the PROPERTY column on SYS.TAB$. I imagine that one day that will creep its way into the standard data dictionary views.
Learning is not a spectator sport 