ALL_TAB_PARTITIONS

ALL_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package for the partitions accessible to the current user.

Note: Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the DBMS_STATS package.

Note:

The following is true for the columns below that include double asterisks (**) in the column description:

The column can display information about segment-level attributes (for simple partitioned tables) or metadata (for composite partitioned tables). In a simple partitioned table, the partition physically contains the data (the segment) in the database. In a composite partitioned table, the partition is metadata and the data itself is stored in the subpartitions.

Column Datatype NULL Description
TABLE_OWNER VARCHAR2(128)   Owner of the table
TABLE_NAME VARCHAR2(128)   Name of the table
COMPOSITE VARCHAR2(3)   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   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)  

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)  

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 AI Database that is dependent on the underlying storage system. See Oracle AI 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   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   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   Date on which this partition was most recently analyzed
BUFFER_POOL VARCHAR2(7)  

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

  • DEFAULT

  • KEEP

  • RECYCLE

  • NULL
FLASH_CACHE VARCHAR2(7)  

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)  

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 NO if statistics have been aggregated from subpartitions or have not been gathered
USER_STATS VARCHAR2(3)   Indicates whether statistics were entered directly by the user (YES) or not (NO)
IS_NESTED VARCHAR2(3)  

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

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)  

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_INDEXING value is used for the newly created subpartition.

  • ON - INDEXING is on.

  • OFF - INDEXING is off.

READ_ONLY VARCHAR2(4)  

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.

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

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

  • LOW

  • MEDIUM

  • HIGH

  • CRITICAL

  • NONE

  • NULL
INMEMORY_DISTRIBUTE VARCHAR2(15)  

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)  

Indicates the compression level for the IM column store:

  • NO MEMCOMPRESS

  • FOR DML

  • FOR QUERY [ LOW | HIGH ]

  • FOR CAPACITY [ LOW | HIGH ]

  • AUTO

  • 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-NULL for ALL_TAB_PARTITIONS.

INMEMORY_DUPLICATE VARCHAR2(13)  

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

  • NO DUPLICATE

  • DUPLICATE

  • DUPLICATE ALL
CELLMEMORY VARCHAR2(24)  

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.
INMEMORY_SERVICE VARCHAR2(12)  

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.

INMEMORY_SERVICE_NAME VARCHAR2(100)   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.
MEMOPTIMIZE_READ VARCHAR2(8)   Indicates whether the table is enabled for Fast Key Based Access (ENABLED) or not (DISABLED)
MEMOPTIMIZE_WRITE VARCHAR2(8)   For internal use only
HIGH_VALUE_CLOB CLOB   High value (boundary) information for the partition, in CLOB format
HIGH_VALUE_JSON JSON   High value (boundary) information for the partition, in JSON format

See Also: