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 HIGH1

  • ARCHIVE LOW1

  • ARCHIVE HIGH1

  • NULL

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)

  

Indicates whether statistics were gathered for the partition as a whole (YES) or they were not gathered (NO). Note that partition statistics are estimated from underlying subpartition statistics when set to NO.

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.

INMEMORY

VARCHAR2(8)

  

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

INMEMORY_PRIORITY2

VARCHAR2(8)

  

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

  • LOW

  • MEDIUM

  • HIGH

  • CRITICAL

  • NONE

  • NULL

INMEMORY_DISTRIBUTE2

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_COMPRESSION2

VARCHAR2(17)

  

Indicates the compression level for the IM column store:

  • NO MEMCOMPRESS

  • FOR DML

  • FOR QUERY [ LOW | HIGH ]

  • FOR CAPACITY [ LOW | HIGH ]

  • NULL3

INMEMORY_DUPLICATE2

VARCHAR2(13)

  

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

  • NO DUPLICATE

  • DUPLICATE

  • DUPLICATE ALL

See Also:

1

Hybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.

2

This column is available starting with Oracle Database 12c Release 1 (12.1.0.2).

3

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.