[20220610][转载]Is my table marked for archive.txt

[20220610][转载]Is my table marked for archive.txt --//https://connor-mcdonald.com/2022/05/25/is-my-table-marked-for-archive/ 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$.我想有一天它会悄悄进入标准数据字典视图。