视图：DBA_TAB_PARTITIONS 分区表视图

ColumnDatatype释义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';

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/houzhiheng/p/15018558.html