ALL_TAB_SUBPARTITIONS

ALL_TAB_SUBPARTITIONS displays, for each table subpartition accessible to the current user, the subpartition name, name of the table and partition to which it belongs, its storage attributes, and statistics generated by the DBMS_STATS package.

Related Views

DBA_TAB_SUBPARTITIONS displays such information for all subpartitions in the database.
USER_TAB_SUBPARTITIONS displays such information for subpartitions 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(128)`	`NOT NULL`	Owner of the table
`TABLE_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the table
`PARTITION_NAME`	`VARCHAR2(128)`		Name of the partition
`SUBPARTITION_NAME`	`VARCHAR2(128)`		Name of the subpartition
`HIGH_VALUE`	`LONG`		Subpartition bound value expression
`HIGH_VALUE_LENGTH`	`NUMBER`	`NOT NULL`	Length of the subpartition bound value expression
`SUBPARTITION_POSITION`	`NUMBER`		Position of the subpartition within the partition
`TABLESPACE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the tablespace containing the subpartition
`PCT_FREE`	`NUMBER`	`NOT NULL`	Minimum percentage of free space in a block
`PCT_USED`	`NUMBER`		Minimum percentage of used space in a block
`INI_TRANS`	`NUMBER`	`NOT NULL`	Initial number of transactions
`MAX_TRANS`	`NUMBER`	`NOT NULL`	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 freelist groups allocated in this segment
`FREELIST_GROUPS`	`NUMBER`		Number of freelist groups allocated in this segment
`LOGGING`	`VARCHAR2(3)`		Indicates whether or not changes to the table are logged: `YES` `NO`
`COMPRESSION`	`VARCHAR2(8)`		Indicates whether this subpartition is compressed (`ENABLED`) or not (`DISABLED`)
`COMPRESS_FOR`	`VARCHAR2(30)`		Default compression for what kind of operations: `BASIC` `ADVANCED` `QUERY LOW` `QUERY HIGH`¹ `ARCHIVE LOW`¹ `ARCHIVE HIGH`¹ `NULL`
`NUM_ROWS`	`NUMBER`		Number of rows in the subpartition
`BLOCKS`	`NUMBER`		Number of blocks in the subpartition
`EMPTY_BLOCKS`	`NUMBER`		Number of empty blocks in the subpartition
`AVG_SPACE`	`NUMBER`		Average space in the subpartition
`CHAIN_CNT`	`NUMBER`		Chain count
`AVG_ROW_LEN`	`NUMBER`		Average row length
`SAMPLE_SIZE`	`NUMBER`		Sample size
`LAST_ANALYZED`	`DATE`		Date on which this table was most recently analyzed
`BUFFER_POOL`	`VARCHAR2(7)`		Buffer pool for this subpartition: `DEFAULT` `KEEP` `RECYCLE` NULL
`FLASH_CACHE`	`VARCHAR2(7)`		Database Smart Flash Cache hint to be used for subpartition blocks: `DEFAULT` `KEEP` `NONE` Solaris and Oracle Linux functionality only.
`CELL_FLASH_CACHE`	`VARCHAR2(7)`		Cell flash cache hint to be used for subpartition blocks: `DEFAULT` `KEEP` `NONE` See Also: Oracle Exadata Storage Server Software documentation for more information
`GLOBAL_STATS`	`VARCHAR2(3)`		Indicates whether statistics were gathered on a subpartition (`YES`) or they were not gathered (`NO`)
`USER_STATS`	`VARCHAR2(3)`		Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`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(3)`		Indicates whether the table subpartition segment has been created (`YES`) or not (`NO`); `N/A` indicates that this table is not subpartitioned
`INDEXING`	`VARCHAR2(3)`		Indicates the indexing property. Possible values: `ON` - Indexing is on for this subpartition `OFF` - Indexing is off for this subpartition
`INMEMORY`	`VARCHAR2(8)`		Indicates whether the In-Memory Column Store (IM column store) is enabled (`ENABLED`) or disabled (`DISABLED`) for this subpartition
`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` `DBY PARTITION` `BY SUBPARTITION`
`INMEMORY_COMPRESSION`²	`VARCHAR2(17)`		Indicates the compression level for the IM column store in an Oracle RAC environment: `NO MEMCOMPRESS` `FOR DML` `FOR QUERY [ LOW \| HIGH ]` `FOR CAPACITY [ LOW \| HIGH ]` `NULL`³
`INMEMORY_DUPLICATE`²			Indicates the duplicate setting for the IM column store in an Oracle RAC environment: `DUPLICATE` `NO DUPLICATE` `DUPLICATE ALL`