視圖：DBA_TAB_PARTITIONS 分區表視圖

 

 

Column Datatype 釋義 Description

TABLE_OWNER

VARCHAR2(128)

 表的owner

Owner of the table

TABLE_NAME

VARCHAR2(128)

 表名

Name of the table

COMPOSITE

VARCHAR2(3)

  指示表是否是複合分區 ( YES) 或不是 ( NO)

Indicates whether the table is composite-partitioned (YES) or not (NO)

PARTITION_NAME

VARCHAR2(128)

 分區名稱

Name of the partition

SUBPARTITION_COUNT

NUMBER

 分區中的子分區數

If this is a composite partitioned table, the number of subpartitions in the partition

HIGH_VALUE

LONG

 分區範圍具體值(類似下邊這種)

TO_DATE(‘ 2000-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

Partition bound value expression

HIGH_VALUE_LENGTH

NUMBER

分區綁定值表達式的長度

Length of the partition bound value expression

PARTITION_POSITION

NUMBER

 分區在表中的位置

Position of the partition within the table

TABLESPACE_NAME

VARCHAR2(30)

 分區所在表空間

Name of the tablespace containing the partition**

PCT_FREE

NUMBER

 塊中可用空間的最小百分比**

Minimum percentage of free space in a block**

PCT_USED

NUMBER

 塊中已用空間的最小百分比**

Minimum percentage of used space in a block**

INI_TRANS

NUMBER

塊中已用空間的最小百分比

Initial number of transactions**

MAX_TRANS

NUMBER

 最大交易數量

Maximum number of transactions**

INITIAL_EXTENT

NUMBER

 初始交易數量

Size of the initial extent in bytes (for a range partition); size of the initial extent in blocks (for a composite partition)**

NEXT_EXTENT

NUMBER

  以位元組為單位的輔助範圍的大小（對於範圍分區）；塊中二級盤區的大小（對於複合分區）

Size of secondary extents in bytes (for a range partition); size of secondary extents in blocks (for a composite partition)**

MIN_EXTENT

NUMBER

  段中允許的最小範圍數

Minimum number of extents allowed in the segment**

MAX_EXTENT

NUMBER

  段中允許的最大範圍數**

Maximum number of extents allowed in the segment**

MAX_SIZE

NUMBER

  段中允許的最大塊數**

Maximum number of blocks allowed in the segment**

PCT_INCREASE

NUMBER

  

擴展區大小增加的百分比**

Percentage increase in extent size**

FREELISTS

NUMBER

  

此段中分配的進程空閑列表數**

Number of process freelists allocated in this segment**

FREELIST_GROUPS

NUMBER

  

此段中分配的空閑列表組數**

Number of freelist groups allocated in this segment**

LOGGING

VARCHAR2(7)

  

指示是否記錄對錶的更改：**

 

Indicates whether or not changes to the table are logged:**

  • NONE – Not specified

    See Also: the *_TAB_SUBPARTITIONS view

  • YES

  • NO

COMPRESSION

VARCHAR2(8)

 分區表的分區的實際壓縮屬性

ENABLED – 壓縮設置已啟用。
DISABLED – 壓縮設置被禁用。

Indicates the actual compression property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE.**

  • NONE – The partition is composite, and a default setting is not specified for compression.

    See Also: the *_TAB_SUBPARTITIONS view

  • ENABLED – The setting for compression is enabled.

  • DISABLED – The setting for compression is disabled.

COMPRESS_FOR

VARCHAR2(30)

 壓縮方式

BASIC
ADVANCED
QUERY LOW
QUERY HIGH
ARCHIVE LOW
ARCHIVE HIGH
NULL

Default compression for what kind of operations:**

  • BASIC

  • ADVANCED

  • QUERY LOW

  • QUERY HIGH

  • ARCHIVE LOW

  • ARCHIVE HIGH

  • NULL

The QUERY_LOW, QUERY_HIGH, ARCHIVE_LOW, and ARCHIVE_HIGH values are associated with Hybrid Columnar Compression, a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.

NUM_ROWS*

NUMBER

  分區中的行數

Number of rows in the partition

BLOCKS*

NUMBER

  

分區中使用的數據塊數

Number of used data blocks in the partition

EMPTY_BLOCKS

NUMBER

  分區中的空（從未使用）數據塊數。僅當您使用DBMS_STATS包收集有關表的統計信息時，才會填充此列。

Number of empty (never used) data blocks in the partition. This column is populated only if you collect statistics on the table using the DBMS_STATS package.

AVG_SPACE*

NUMBER

  

分配給分區的數據塊中的平均可用空間量（以位元組為單位）

Average amount of free space, in bytes, in a data block allocated to the partition

CHAIN_CNT*

NUMBER

  

分區中從一個數據塊鏈接到另一個數據塊的行數，或已遷移到新塊，需要鏈接以保留舊 ROWID 的行數

Number of rows in the partition that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID

AVG_ROW_LEN*

NUMBER

  

分區中一行的平均長度（以位元組為單位）

Average length of a row in the partition (in bytes)

SAMPLE_SIZE

NUMBER

  

用於分析此分區的樣本大小

Sample size used in analyzing this partition

LAST_ANALYZED

DATE

  

最近analyzed此分區的日期

Date on which this partition was most recently analyzed

BUFFER_POOL

VARCHAR2(7)

  

用於分區塊的緩衝池：

  • DEFAULT

  • KEEP

  • RECYCLE

  • NULL

Buffer pool to be used for the partition blocks:**

  • DEFAULT

  • KEEP

  • RECYCLE

  • NULL

FLASH_CACHE

VARCHAR2(7)

  

用於分區塊的數據庫智能閃存緩存提示：

  • DEFAULT

  • KEEP

  • NONE

僅限 Solaris 和 Oracle Linux 功能。

Database Smart Flash Cache hint to be used for partition blocks:**

  • DEFAULT

  • KEEP

  • NONE

Solaris and Oracle Linux functionality only.

CELL_FLASH_CACHE

VARCHAR2(7)

  

用於分區塊的單元閃存緩存提示：

  • DEFAULT

  • KEEP

  • NONE

另請參閱： Oracle Exadata 存儲服務器軟件文檔了解更多信息

Cell flash cache hint to be used for partition blocks:**

  • DEFAULT

  • KEEP

  • NONE

See Also: Oracle Exadata Storage Server Software documentation for more information

GLOBAL_STATS

VARCHAR2(3)

 是否收集統計信息

 

GLOBAL_STATS will be YES if statistics have been gathered or NOif statistics have been aggregated from subpartitions or have not been gathered

USER_STATS

VARCHAR2(3)

  

指示統計數據是否由用戶直接輸入 ( YES) 或不是 ( NO)

Indicates whether statistics were entered directly by the user (YES) or not (NO)

IS_NESTED

VARCHAR2(3)

  

指示這是否是嵌套表分區 ( YES) 或不是 ( NO)

Indicates whether this is a nested table partition (YES) or not (NO)

See Also: the *_NESTED_TABLES view for the parent table name/column

PARENT_TABLE_PARTITION

VARCHAR2(128)

  

父表對應的分區

Parent table’s corresponding partition

See Also: the *_NESTED_TABLES view for the parent table name/column

INTERVAL

VARCHAR2(3)

  

指示分區是否在區間分區表的區間部分 ( YES) 或分區是否在範圍部分 ( NO)

Indicates whether the partition is in the interval section of an interval partitioned table (YES) or whether the partition is in the range section (NO)

SEGMENT_CREATED

VARCHAR2(4)

  

指示簡單分區表的分區的實際段創建屬性，或複合分區表的子分區的默認值（如果指定），否則為NONE.**
對於簡單的分區表，此列指示是否創建了 ( YES)段( NO)。
對於複合分區表，此列指示是否顯式指定了默認段創建屬性。可能的值：
NONE- 在分區級別沒有指定默認的段創建屬性。此值僅針對複合分區出現，並被視為未指定的值。
YES – 立即段創建在分區級別明確指定，並將用作其所有子分區的默認值。
NO – 在分區級別明確指定了延遲段創建，並將用作其所有子分區的默認值。

Indicates the actual segment creation property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE.**

For a simple partitioned table, this column indicates whether a segment was created (YES) or not (NO).

For composite partitioned tables, this column indicates whether or not a default segment creation property was explicitly specified. Possible values:

  • NONE – No default segment creation property was specified at the partition level. This value appears only for composite partitions, and is treated as an unspecified value.

  • YES – Immediate segment creation was explicitly specified at the partition level and will be used as the default for all of its subpartitions.

  • NO – Deferred segment creation was explicitly specified at the partition level and will be used as the default for all of its subpartitions.

INDEXING

VARCHAR2(4)

  

指示簡單分區表的分區的實際索引屬性，或複合分區表的子分區的默認值（如果指定），否則為 NONE.**
可能的值：
NONE – 分區是複合的，沒有為索引指定默認設置。
此值僅針對複合分區出現，並被視為未指定的值。當用戶向表中添加子分區時，由於未指定分區的默認值，因此該ALL_PART_TABLES.DEF_INDEXING值用於新創建的子分區。
ON-INDEXING開啟。
OFF-INDEXING關了

Indicates the actual indexing property for a partition of a simple partitioned table, or the default (if specified) for subpartitions for composite partitioned tables, otherwise NONE.**

Possible values:

  • NONE – The partition is composite, and a default setting is not specified for indexing.

    This value appears only for composite partitions, and is treated as an unspecified value. When a user adds a subpartition to a table, since the defaults for the partition are unspecified, the ALL_PART_TABLES.DEF_INDEXINGvalue is used for the newly created subpartition.

  • ON – INDEXING is on.

  • OFF – INDEXING is off.

READ_ONLY

VARCHAR2(4)

  

表示分區的默認設置：
YES：分區的默認設置是只讀的。
NO：分區的默認設置是讀/寫。
NONE: 沒有為分區指定默認設置。
此列從 Oracle Database 12 c第 2 版 (12.2.0.1)開始可用。

Indicates the default setting for the partition:

  • YES: The default setting for the partition is read-only.

  • NO: The default setting for the partition is read/write.

  • NONE: No default setting is specified for the partition.

This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).

INMEMORY

VARCHAR2(8)

  

指示是否為此分區啟用 ( ENABLED) 或禁用 ( DISABLED) 內存列存儲（IM 列存儲）

Indicates whether the In-Memory Column Store (IM column store) is enabled (ENABLED) or disabled (DISABLED) for this partition

INMEMORY_PRIORITY

VARCHAR2(8)

  

表示 In-Memory Column Store（IM 列存儲）填充的優先級。可能的值：
LOW
MEDIUM
HIGH
CRITICAL
NONE
NULL

Indicates the priority for In-Memory Column Store (IM column store) population. Possible values:

  • LOW

  • MEDIUM

  • HIGH

  • CRITICAL

  • NONE

  • NULL

INMEMORY_DISTRIBUTE

VARCHAR2(15)

  

指示 IM 列存儲在 Oracle Real Application Clusters (Oracle RAC) 環境中的分佈方式：
AUTO
BY ROWID RANGE
BY PARTITION
BY SUBPARTITION

Indicates how the IM column store is distributed in an Oracle Real Application Clusters (Oracle RAC) environment:

  • AUTO

  • BY ROWID RANGE

  • BY PARTITION

  • BY SUBPARTITION

INMEMORY_COMPRESSION

VARCHAR2(17)

  

表示 IM 列存儲的壓縮級別：
NO MEMCOMPRESS
FOR DML
FOR QUERY [ LOW | HIGH ]
FOR CAPACITY [ LOW | HIGH ]
NULL
此列的值基於表的段所在位置。例如，如果表已分區並為 IM 列存儲啟用，則值為NULLforALL_TABLES但非NULLfor ALL_TAB_PARTITIONS。

Indicates the compression level for the IM column store:

  • NO MEMCOMPRESS

  • FOR DML

  • FOR QUERY [ LOW | HIGH ]

  • FOR CAPACITY [ LOW | HIGH ]

  • NULL

This column has a value based on where the segments lie for a table. For example, if the table is partitioned and is enabled for the IM column store, the value is NULL for ALL_TABLES but non-NULLfor ALL_TAB_PARTITIONS.

INMEMORY_DUPLICATE

VARCHAR2(13)

  

指示 Oracle RAC 環境中 IM 列存儲的重複設置：
NO DUPLICATE
DUPLICATE
DUPLICATE ALL

Indicates the duplicate setting for the IM column store in an Oracle RAC environment:

  • NO DUPLICATE

  • DUPLICATE

  • DUPLICATE ALL

CELLMEMORY

VARCHAR2(24)

  

存儲單元閃存緩存中的列壓縮值。可能的值：
ENABLED: Oracle Exadata Storage 會自動決定是否以列形式緩存
DISABLED: Oracle Exadata Storage 被阻止以列形式緩存
NO CACHECOMPRESS：Oracle Exadata Storage 將以 HCC 格式緩存（無重新壓縮）
FOR QUERY：Oracle Exadata Storage 將以 INMEMORY 查詢高格式重新壓縮和緩存
FOR CAPACITY：Oracle Exadata Storage 將以 INMEMORY 容量低格式重新壓縮和緩存
此列旨在與 Oracle Exadata 一起使用。
此列從 Oracle Database 12 c第 2 版 (12.2.0.1)開始可用。

The value for columnar compression in the storage cell flash cache. Possible values:

  • ENABLED: Oracle Exadata Storage will decide automatically whether to cache in columnar form

  • DISABLED: Oracle Exadata Storage is prevented from caching in columnar form

  • NO CACHECOMPRESS: Oracle Exadata Storage will cache in HCC format (no recompression)

  • FOR QUERY: Oracle Exadata Storage will recompress and cache in INMEMORY query high format

  • FOR CAPACITY: Oracle Exadata Storage will recompress and cache in INMEMORY capacity low format

This column is intended for use with Oracle Exadata.

This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).

INMEMORY_SERVICE

VARCHAR2(12)

  

指示如何在各種實例上填充 IM 列存儲。可能的值為：
DEFAULT：數據被填充到PARALLEL_INSTANCE_GROUP初始化參數指定的所有實例上。如果未設置該參數，則會在所有實例上填充數據。這是默認設置。
NONE：數據未在任何實例上填充。
ALL：無論PARALLEL_INSTANCE_GROUP初始化參數的值如何，都會在所有實例上填充數據。
USER_DEFINED：僅在用戶指定的服務處於活動狀態的實例上填充數據。與此對應的服務名稱存儲在INMEMORY_SERVICE_NAME列中。
此列從 Oracle Database 12 c第 2 版 (12.2.0.1)開始可用。

Indicates how the IM column store is populated on various instances. The possible values are:

  • DEFAULT: Data is populated on all instances specified with the PARALLEL_INSTANCE_GROUP initialization parameter. If that parameter is not set, then the data is populated on all instances. This is the default.

  • NONE: Data is not populated on any instance.

  • ALL: Data is populated on all instances, regardless of the value of the PARALLEL_INSTANCE_GROUP initialization parameter.

  • USER_DEFINED: Data is populated only on the instances on which the user-specified service is active. The service name corresponding to this is stored in the INMEMORY_SERVICE_NAME column.

This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).

INMEMORY_SERVICE_NAME

VARCHAR2(100)

  

指示應填充 IM 列存儲的服務的服務名稱。僅當對應的INMEMORY_SERVICE是時，此列才具有值USER_DEFINED。在所有其他情況下，此列為空。
此列從 Oracle Database 12 c第 2 版 (12.2.0.1)開始可用。

Indicates the service name for the service on which the IM column store should be populated. This column has a value only when the corresponding INMEMORY_SERVICE is USER_DEFINED. In all other cases, this column is null.

This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).

 

常用查詢命令：

查看某張表的所有分區信息
select * from dba_tab_partitions where table_owner='HZH' and table_name='PARTITIONED';

查看一張表的各分區大小，壓縮信息
select /*+parallel(4)*/ p.table_owner,p.table_name,p.partition_name,s.bytes/1024/1024/1024 gb,s.tablespace_name,p.compression,p.compress_for,p.partition_position,
RANK() OVER(PARTITION BY TABLE_OWNER,TABLE_NAME ORDER BY PARTITION_POSITION DESC) rn from dba_tab_partitions p,dba_segments s where p.table_name=s.segment_name and p.table_owner=s.owner and p.partition_name=s.partition_name and s.SEGMENT_NAME='&tbname';    

查看一張表的high_value分區範圍
set linesize 999
select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_owner='HZH' and table_name='PARTITIONED';

 

 

 

Tags:

VirMach 便宜 VPS

QNews

QNews