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	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 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(12)`	Default compression for what kind of operations: `BASIC` `OLTP` `QUERY LOW`^Foot 1 `QUERY HIGH`^Footref 1 `ARCHIVE LOW`^Footref 1 `ARCHIVE HIGH`^Footref 1 `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 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`)
`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(30)`	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 whether the table partition segment has been created (`YES`) or not (`NO`); `N/A` indicates that this table is subpartitioned and no segment exists at the partition level

^Footnote 1Hybrid 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.