3.115 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.

Related Views

  • DBA_TAB_PARTITIONS displays such information for all partitions in the database.

  • USER_TAB_PARTITIONS displays such information for the partitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.

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

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 ]

  • 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_READFoot 1

VARCHAR2(8)

Indicates whether the table is enabled for Fast Key Based Access (ENABLED) or not (DISABLED)

MEMOPTIMIZE_WRITEFoot 1

VARCHAR2(8)

For internal use only

Footnote 1 This column is available starting with Oracle Database 18c.

See Also: