1. Database Reference
  2. Static Data Dictionary Views
  3. Static Data Dictionary Views: ALL_PART_COL_STATISTICS to DATABASE_PROPERTIES
  4. ALL_TAB_PARTITIONS

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.

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

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.

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

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.

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

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.

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

See Also: