a large bank safe locked shut

Is my table marked for archive?

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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.