Skip Headers
Oracle® Database Reference
12c Release 1 (12.1)

E17615-23
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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

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:**
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.


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

See Also: