[20220610][轉載]Is my table marked for archive.txt

--//

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.

在12c中，我們引入了一個名為行存檔的特性，這是一種將我們用來構建的典型存檔模型與自制的解決方案整合到資料庫中的方法。實際
上，可以將表中的一組行標記為"home grown"，即使它們保留在表中，(預設情況下)對查詢不再可見。我不會詳細介紹這個特性，因為很
多人已經這樣做了。

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.

然而，使用行歸檔的一個小特性是，如果一個表被標記為這樣的，就不會立即明顯。例如，如果我建立了一個簡單的表並新增了行歸檔工
具。

TTT@192.168.2.7:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

TTT@192.168.2.7:1521/orcl> create table t ( x int );
Table created.

TTT@192.168.2.7:1521/orcl> alter table t row archival;
Table altered.

TTT@192.168.2.7:1521/orcl> select * from user_tables where table_name = 'T'
2 @ 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                      : 0
BLOCKS                        : 0
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 0
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   : 0
LAST_ANALYZED                 : 2022-06-10 09:12:01
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                  : YES
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
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
PL/SQL procedure successfully completed.

One option I suppose to detect if a table has row archival is to try add it again, ie
我想檢測一個表是否有行歸檔的一個選項是嘗試再次新增它，即

TTT@192.168.2.7:1521/orcl> 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.

目前，我認為檢測行歸檔的唯一方法是在表中查詢是否存在一個名為ORA_ARCHIVE_STATE的列。這是一個隱藏的列，所以您需要查詢
USER_TAB_COLS，而不是USER_TAB_COLUMNS。

TTT@192.168.2.7:1521/orcl> select column_name, hidden_column from   user_tab_cols where table_name = 'T';
COLUMN_NAME          HIDDEN
-------------------- ------
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.
這是SYS中的資料。在我新增行歸檔之前，為我的表T的TAB$。

TTT@192.168.2.7:1521/orcl> select object_id from dba_objects where object_name='T';
OBJECT_ID
----------
    330057

TTT@192.168.2.7:1521/orcl> select * from sys.tab$ where obj# = 330057
2 @ pr
==============================
OBJ#                          : 330057
DATAOBJ#                      : 330057
TS#                           : 5
FILE#                         : 0
BLOCK#                        : 0
BOBJ#                         :
TAB#                          :
COLS                          : 1
CLUCOLS                       :
PCTFREE$                      : 10
PCTUSED$                      : 40
INITRANS                      : 1
MAXTRANS                      : 255
FLAGS                         : 1073742353
AUDIT$                        : --------------------------------------
ROWCNT                        : 0
BLKCNT                        : 0
EMPCNT                        : 0
AVGSPC                        : 0
CHNCNT                        : 0
AVGRLN                        : 0
AVGSPC_FLB                    : 0
FLBCNT                        : 0
ANALYZETIME                   : 2022-06-10 09:12:01
SAMPLESIZE                    : 0
DEGREE                        :
INSTANCES                     :
INTCOLS                       : 3
KERNELCOLS                    : 3
PROPERTY                      : 140755205095424
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRIGFLAG                      : 0
SPARE1                        : 736
SPARE2                        :
SPARE3                        :
SPARE4                        :
SPARE5                        :
SPARE6                        : 2022-06-10 01:10:35
SPARE7                        :
SPARE8                        :
SPARE9                        :
SPARE10                       :
ACDRFLAGS                     :
ACDRTSOBJ#                    :
ACDRDEFAULTTIME               :
ACDRROWTSINTCOL#              : 0
PL/SQL procedure successfully completed.

And here is the data in SYS.TAB$ for my table T after I added row archival.
這是SYS中的資料。在我新增了行歸檔後，為我的表T的TAB$。
--//我測試加上好像無法取消。
TTT@192.168.2.7:1521/orcl> create table t1 ( x int );
Table created.

TTT@192.168.2.7:1521/orcl> select object_id from dba_objects where object_name='T1' and owner='TTT';
OBJECT_ID
----------
    330062

TTT@192.168.2.7:1521/orcl> select * from sys.tab$ where obj# = 330062
2 @ pr
==============================
OBJ#                          : 330062
DATAOBJ#                      : 330062
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                        : 2022-06-10 01:19:54
SPARE7                        :
SPARE8                        :
SPARE9                        :
SPARE10                       :
ACDRFLAGS                     : 0
ACDRTSOBJ#                    : 0
ACDRDEFAULTTIME               :
ACDRROWTSINTCOL#              : 0

PL/SQL procedure successfully completed.

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 ??

那麼這個數字不會特別，除非你花週末研究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.

因此，表的行歸檔由SYS屬性列的第48位表示。TAB$.我想有一天它會悄悄進入標準資料字典檢視。

[20220610][轉載]Is my table marked for archive.txt

相關文章