Oracle-Style Partition Views in YugabyteDB: USER_TAB_PARTITIONS and ALL_TAB_PARTITIONS

If you’re migrating from Oracle and miss USER_TAB_PARTITIONS and ALL_TAB_PARTITIONS, you can recreate them in YugabyteDB with two simple views over the system catalogs. They expose table_name, partition_name, a readable high_value (the bound), and partition_position (the natural order).

Why?

Oracle DBAs are used to handy data dictionary views for partitions. In YugabyteDB, partition metadata lives in system catalogs (pg_inherits, pg_class, pg_namespace) and the human-readable bound comes from pg_get_expr(relpartbound, oid). Wrapping these in Oracle-like views smooths migrations and lets existing scripts keep working with minimal changes.

Create the Views

● USER_TAB_PARTITIONS (current schema only)

This mimics Oracle’s USER_TAB_PARTITIONS (no owner/schema column; implicitly scoped to the current schema).

				
					CREATE OR REPLACE VIEW user_tab_partitions AS
SELECT
  p.relname                          AS table_name,          -- parent partitioned table
  c.relname                          AS partition_name,      -- child partition
  pg_get_expr(c.relpartbound, c.oid) AS high_value,          -- 'FOR VALUES ...' text
  i.inhseqno                         AS partition_position   -- natural order (like Oracle's PARTITION_POSITION)
FROM pg_inherits i
JOIN pg_class      c  ON c.oid = i.inhrelid      -- child
JOIN pg_class      p  ON p.oid = i.inhparent     -- parent
JOIN pg_namespace  np ON np.oid = p.relnamespace
WHERE p.relkind = 'p'                             -- parent is a partitioned table
  AND np.nspname = current_schema                 -- scope to current schema (Oracle USER_* behavior)
ORDER BY p.relname, i.inhseqno;

Example usage (Oracle-style):

				
					SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'stored_credentials_catalog'
ORDER BY partition_position;

● ALL_TAB_PARTITIONS (all schemas, includes owner)

This mimics Oracle’s ALL_TAB_PARTITIONS by including the schema/owner.

				
					CREATE OR REPLACE VIEW all_tab_partitions AS
SELECT
  np.nspname                        AS table_owner,          -- schema (Oracle's TABLE_OWNER)
  p.relname                          AS table_name,
  c.relname                          AS partition_name,
  pg_get_expr(c.relpartbound, c.oid) AS high_value,
  i.inhseqno                         AS partition_position
FROM pg_inherits i
JOIN pg_class      c  ON c.oid = i.inhrelid
JOIN pg_class      p  ON p.oid = i.inhparent
JOIN pg_namespace  np ON np.oid = p.relnamespace
WHERE p.relkind = 'p'
ORDER BY np.nspname, p.relname, i.inhseqno;

Example usage:

				
					SELECT table_owner, table_name, partition_name, high_value
FROM all_tab_partitions
WHERE table_owner = 'public' AND table_name = 'stored_credentials_catalog'
ORDER BY partition_position;

Column Mapping (Oracle → YB/Postgres)

● TABLE_OWNER → nspname (schema) in ALL_TAB_PARTITIONS; omitted in USER_TAB_PARTITIONS
● TABLE_NAME → p.relname
● PARTITION_NAME → c.relname
● HIGH_VALUE → pg_get_expr(c.relpartbound, c.oid) (e.g., FOR VALUES FROM (...) TO (...), IN (...))
● PARTITION_POSITION → i.inhseqno

Notes & Gotchas

● Readable bounds: pg_get_expr(relpartbound, oid) prints the exact DDL-style clause:
- ○ Range example: FOR VALUES FROM ('2024-09-01') TO ('2024-10-01')
- ○ List example: FOR VALUES IN ('east','west')
- ○ MINVALUE/MAXVALUE are represented inside the expression when used.
● Case sensitivity: If you want Oracle-style UPPERCASE column names in the result set, wrap aliases in double quotes (e.g., AS "PARTITION_NAME"). Otherwise YugabyteDB will present them in lowercase.
● Subpartitions: YugabyteDB YSQL models subpartitioning as partitions of partitions. You can reuse the same views; to explore deeper levels, join again on pg_inherits from the child as needed.
● Security: Expose these views in a shared schema if multiple roles should use them. You may also add GRANT SELECT ON ... as appropriate.

Testing Quickstart

				
					-- Make a tiny demo:
CREATE TABLE demo_parent (d date) PARTITION BY RANGE (d);
CREATE TABLE demo_p2025_09 PARTITION OF demo_parent FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE demo_pmax     PARTITION OF demo_parent FOR VALUES FROM ('2025-10-01') TO (MAXVALUE);

-- Query like Oracle:
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'demo_parent'
ORDER BY partition_position;

Example:

				
					yugabyte=# CREATE TABLE demo_parent (d date) PARTITION BY RANGE (d);
CREATE TABLE

yugabyte=# CREATE TABLE demo_p2025_09 PARTITION OF demo_parent FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE

yugabyte=# CREATE TABLE demo_pmax PARTITION OF demo_parent FOR VALUES FROM ('2025-10-01') TO (MAXVALUE);
CREATE TABLE

yugabyte=# SELECT partition_name, high_value
yugabyte-# FROM user_tab_partitions
yugabyte-# WHERE table_name = 'demo_parent'
yugabyte-# ORDER BY partition_position;
 partition_name |                    high_value
----------------+--------------------------------------------------
 demo_p2025_09  | FOR VALUES FROM ('2025-09-01') TO ('2025-10-01')
 demo_pmax      | FOR VALUES FROM ('2025-10-01') TO (MAXVALUE)
(2 rows)

Wrap-Up

These two views give you Oracle-style partition introspection with familiar columns and ordering, powered by Postgres/YugabyteDB catalogs. Drop them into your migration toolkit and reuse your Oracle scripts with minimal edits.

Have Fun!