DBA_TAB_PARTITIONS

Oracle 11gR1
DBA_TAB_PARTITIONS provides the partition-level partitioning information, partition storage parameters, and partition statistics determined by ANALYZE statements for all partitions in the database. Its columns are the same as those in ALL_TAB_PARTITIONS.

Related Views

· ALL_TAB_PARTITIONS provides partition-level partitioning information, partition storage parameters, and partition statistics collected by ANALYZE statements for partitions accessible to the current user.

· USER_TAB_PARTITIONS provides such information for partitions of all partitioned objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
TABLE_OWNER	VARCHAR2(30)	NOT NULL	Table owner
TABLE_NAME	VARCHAR2(30)	NOT NULL	Table name
COMPOSITE	VARCHAR2(3)		YES if the table is composite-partitioned, NO if it is not composite-partitioned
PARTITION_NAME	VARCHAR2(30)		Partition name
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	NOT NULL	Length of partition bound value expression
PARTITION_POSITION	NUMBER	NOT NULL	Position of the partition within the table
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the partition
PCT_FREE	NUMBER	NOT NULL	Minimum percentage of free space in a block
PCT_USED	NUMBER	NOT NULL	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	NOT NULL	Minimum number of extents allowed in the segment
MAX_EXTENT	NUMBER	NOT NULL	Maximum number of extents allowed in the segment
PCT_INCREASE	NUMBER	NOT NULL	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(3)		Logging attribute of partition
COMPRESSION	VARCHAR2(8)		Indicates whether this partition is compressed (ENABLED) or not (DISABLED)
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)		The default buffer pool to be used for the partition blocks
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)

Note:

1.

select table_owner, table_name, partition_name, tablespace_name, logging, compression, last_analyzed, buffer_pool

from DBA_TAB_PARTITIONS;

TABLE_ OWNER	TABLE_NAME	PARTITION_NAME	TABLESPACE_ NAME	LOGGING	COMPRESSION	LAST_ ANALYZED	BUFFER_ POOL
SYS	WRH$_LATCH_MISSES_SUMMARY	WRH$_LATCH__2334540272_19092	SYSAUX	YES	DISABLED	2008-08-19 11:29:17 PM	DEFAULT
SYS	WRH$_LATCH	WRH$_LATCH_2334540272_19092	SYSAUX	YES	DISABLED	2008-08-19 11:29:17 PM	DEFAULT
SYS	WRH$_WAITSTAT	WRH$_WAITST_2334540272_19092	SYSAUX	YES	DISABLED	2008-08-19 11:29:13 PM	DEFAULT
SYSTEM	LOGMNR_INDCOMPART$	P_LESSTHAN100	SYSAUX	YES	DISABLED	2006-09-23 7:41:04 AM	DEFAULT
SYSTEM	LOGMNR_INDSUBPART$	P_LESSTHAN100	SYSAUX	YES	DISABLED	2006-09-23 7:41:04 AM	DEFAULT

Oracle data dictionary views

More Oracle DBA tips, please visit Oracle DBA Tips