分区表（三）--查询分区边界

最新推荐文章于 2025-11-12 09:54:46 发布

转载最新推荐文章于 2025-11-12 09:54:46 发布 · 779 阅读

文章标签：

#oracle #分区表

Oracle 专栏收录该内容

62 篇文章

订阅专栏

本文提供了一段复杂的SQL查询语句，用于从Oracle数据库中检索分区表的相关信息，包括表名、分区名、分区位置及高值等。此查询还涉及到了分区键列的所有者、名称及数据类型等详细信息。

SELECT A.TABLE_NAME,A.PARTITION_NAME,A.PARTITION_POSITION,a.high_value, K.OWNER, K.NAME, K.COLUMN_NAME, TC.DATA_TYPE
FROM (SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM (SELECT /*+ rule */
T1.TABLE_NAME, T1.PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE, ROW_NUMBER() OVER(PARTITION BY T1.TABLE_OWNER, T1.TABLE_NAME ORDER BY PARTITION_POSITION DESC) RN
FROM DBA_TAB_PARTITIONS T1, DBA_PART_TABLES T2
WHERE T1.TABLE_OWNER = '&name' AND
T1.TABLE_OWNER = T2.OWNER AND
T1.TABLE_NAME = T2.TABLE_NAME AND
T2.PARTITIONING_TYPE = 'RANGE')
WHERE RN = 1) A, DBA_PART_KEY_COLUMNS K, DBA_TAB_COLUMNS TC
WHERE K.OWNER ='&name' AND K.NAME = A.TABLE_NAME AND
K.COLUMN_POSITION = 1 AND K.OBJECT_TYPE = 'TABLE' AND
K.OWNER = TC.OWNER AND K.NAME = TC.TABLE_NAME AND
K.COLUMN_NAME = TC.COLUMN_NAME ;