DBA Views for Partitioned Objects

Oracle partitioning enables very large tables and indexes to be decomposed into smaller, independently manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics. From the perspective of the application, only one schema object exists — SQL statements require no modification to access partitioned tables.

A useful analogy: an HR manager has one large box containing all employee folders organized by hire date. Queries for employees hired in a specific month require scanning the entire box. A partitioning strategy replaces the single box with many smaller boxes, one per month. When asked for June hires, the manager retrieves only the June box. If one box is damaged, the others remain available. The same principle applies to Oracle partitioned tables — partition unavailability does not affect the object as a whole.

The four primary benefits of partitioned objects are:

1. Increased availability: the query optimizer automatically removes unreferenced partitions from the query plan, so queries against available partitions are unaffected when other partitions are offline.
2. Easier administration: DDL statements can manipulate individual partitions rather than entire tables or indexes. Resource-intensive tasks such as index rebuilds can be broken into per-partition operations. Dropping a partition avoids executing large volumes of DELETE statements and resets the partition high water mark instantaneously.
3. Reduced contention in OLTP systems: DML is distributed across many segments rather than one, reducing latch and freelist contention on the segment header.
4. Enhanced query performance in data warehouses: partition pruning limits ad hoc queries to only the relevant partitions. A sales table with a million rows partitioned by quarter restricts a quarterly query to one quarter of the data.

When working with partitioned objects in Oracle, the DBA partition views are the primary diagnostic and administrative tool. These data dictionary views are similar in structure to DBA_TABLES and DBA_INDEXES but contain partition-level information rather than object-level information. Query and analyze these views to monitor partition health and decide when to reorganize individual partitions.

If the database contains tables with LOB columns, two additional views apply: DBA_LOB_PARTITIONS tracks LOB segments stored within partitioned tables, and DBA_LOB_SUBPARTITIONS tracks LOB segments within composite partitions. Query these views when partitioned tables include CLOB, BLOB, or BFILE columns.

To list all partition-related views available in the data dictionary:

SELECT object_name
FROM   dba_objects
WHERE  object_name LIKE '%PARTITION%'
ORDER BY object_name;

DBA_TAB_PARTITIONS provides partition-level partitioning information, partition storage parameters, and partition statistics gathered by ANALYZE or DBMS_STATS for all partitions in the database. It is the primary view for monitoring partitioned table health across the entire instance.

Three related views provide the same information at different scopes:

• DBA_TAB_PARTITIONS: all partitions in the database; requires DBA privilege or SELECT_CATALOG_ROLE.
• ALL_TAB_PARTITIONS: partitions accessible to the current user, regardless of ownership. Includes partitions on tables the current user has been granted access to.
• USER_TAB_PARTITIONS: partitions of all partitioned objects owned by the current user. Does not display the OWNER column.

The column set is identical across all three views. The full column reference for DBA_TAB_PARTITIONS / ALL_TAB_PARTITIONS / USER_TAB_PARTITIONS is shown below.

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
PARTITION_NAME	VARCHAR2(30)		Partition name
SUBPARTITION_COUNT	NUMBER		Number of subpartitions in the partition (composite partitions only)
HIGH_VALUE	LONG		Partition bound value expression. Note: the LONG datatype in this column is a known limitation — use DBMS_METADATA or cast to VARCHAR2 for programmatic access in Oracle 23ai. See Oracle Partitioned Objects for migration guidance.
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 transaction entries per block
MAX_TRANS	NUMBER	NOT NULL	Maximum number of transaction entries per block
INITIAL_EXTENT	NUMBER		Initial extent size in bytes (range partition) or blocks (composite partition)
NEXT_EXTENT	NUMBER		Secondary extent size in bytes (range partition) or blocks (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 the partition (YES or NO)
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 for partition blocks
GLOBAL_STATS	VARCHAR2(3)		YES if statistics were collected for the partition as a whole; NO if estimated from subpartition statistics
USER_STATS	VARCHAR2(3)		YES if statistics were entered directly by the user; NO if not

Partitioned views were introduced in late Oracle 7 releases. A partitioned view joins several tables — identical in structure except for name — through a single view, making them behave as one logical table. Each component table holds a subset of values, typically a range of values in a key column.

Advantages of Partitioned Views

1. Each component table is separately indexed; all indexes can be scanned in parallel.
2. Oracle skips component tables that cannot produce rows satisfying the query predicate.
3. Partition boundaries can be as sophisticated as CHECK constraints allow.
4. With the parallel query option, component tables can be scanned in parallel.

Disadvantages of Partitioned Views

1. The view itself cannot be updated; only the underlying component tables can be modified directly.
2. No master index — each component table is indexed separately. This makes partitioned views suitable for DSS and data warehouse workloads but unsuitable for OLTP.
3. Partitions can overlap, which introduces data integrity complications that require application- level enforcement.

Partitioned views are a legacy technique rarely used today. Oracle introduced native table partitioning in Oracle 8, which provides far more functionality with significantly less administrative overhead. Partitioned views became obsolete at that point. The only reason to consider them is if the Oracle partitioning option license is unavailable and reduced functionality is acceptable. The Oracle documentation for partitioned views dates from Oracle 7, which was in use in 1998.^[1]

One of the most valuable features of partitioning is the ability to reorganize individual partitions independently from the rest of the table. The following three columns in DBA_TAB_PARTITIONS are the primary indicators of when a partition needs reorganization:

Column	Description	Reorganization signal
NUM_ROWS	Number of rows in the partition	A sudden drop relative to BLOCKS suggests a large DELETE left the partition high water mark elevated — reorganize to reclaim space
AVG_ROW_LEN	Average row length including row overhead	A rising value relative to the previous LAST_ANALYZED snapshot suggests row migration from insufficient PCTFREE — increase PCTFREE and reorganize
CHAIN_CNT	Number of chained rows in the partition	Any value above zero warrants investigation; elevated values require PCTFREE adjustment and reorganization

To reorganize a single partition without affecting other partitions in the table:

-- Reorganize one partition, reset its HWM, and rebuild its local index
ALTER TABLE sales_data MOVE PARTITION p2023 TABLESPACE data_2023;
ALTER INDEX sales_data_idx REBUILD PARTITION p2023;

This targets only the affected partition. The remaining partitions — p2024 and p2025 — stay fully online and accessible throughout the operation. This is one of the most significant advantages of partitioned object management over managing a single monolithic table segment.

There are two types of partition indexes — global and local. The next lesson examines global partitioned indexes and how they complement partitioned tables.

[1] Partition maintenance operation: A partition-related operation such as adding, exchanging, merging, or splitting table partitions.