ALL_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics collected by ANALYZE statements 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.

Column Datatype NULL Description
TABLE_OWNER VARCHAR2(30)   Owner of the table
TABLE_NAME VARCHAR2(30)   Name of the table
COMPOSITE VARCHAR2(3)   Indicates whether the table is composite-partitioned (YES) or not (NO)
PARTITION_NAME VARCHAR2(30)   Name of the partition
SUBPARTITION_COUNT NUMBER   If this is a Local index on a table partitioned using a Composite method, 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 whether compression is enabled or disabled for this partition:
  • NONE - Not specified

    See Also: the *_TAB_SUBPARTITIONS view



COMPRESS_FOR VARCHAR2(18)   Default compression for what kind of operations:


  • NULL

NUM_ROWS NUMBER   Number of rows in the partition
BLOCKS NUMBER   Number of used blocks in the partition
EMPTY_BLOCKS NUMBER   Number of empty (never used) blocks in the partition
AVG_SPACE NUMBER   Average available free space in the partition
CHAIN_CNT NUMBER   Number of chained rows in the partition
AVG_ROW_LEN NUMBER   Average row length, including row overhead
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)   Default buffer pool to be used for the partition blocks:

  • KEEP


  • NULL

GLOBAL_STATS VARCHAR2(3)   Indicates whether statistics were collected for the partition as a whole (YES) or were estimated from statistics on underlying subpartitions (NO)
USER_STATS VARCHAR2(3)   Indicates whether statistics were entered directly by the user (YES) or not (NO)