Oracle查询Interval partition分区表内数据（转载）

转载

mob604756ea26ec 2020-10-29 14:06:00

本文对于Interval partition分区表内查询数据的方法提供了可行方案，并在测试环境进行验证。特别的，本文提供了2种MOS上的方法（福利），并将相关文章附在文末，供没有MOS账号的朋友参考学习。

本文基于一个已经创建的分区表进行测试，测试所用的表为BP_VOUCHER_HISTORY表，以月作为分区，系统自动命名分区。

1.查看分区信息

1	`select` `table_name,partition_name,high_value,partition_position,num_rows` `from` `dba_tab_partitions` `where` `table_owner=``'CAMS_CORE'` `and` `table_name=``'BP_VOUCHER_HISTORY'``;`

Oracle查询Interval partition分区表内数据（转载）_分区表

注：dba_tab_partitions | all_tab_partitions | user_tab_partitions表都可以

2.查看分区内数据（以SYS_P82分区为例）

（1）如果 知道分区的名字 ，可以直接查询对应的分区名

SYS@cams> select count(*) from cams_core.bp_voucher_history partition(SYS_P82);

COUNT(*)

----------

2844459

（2）如果 不知道分区的名字，但是知道分区主键的字段值范围 ，可以基于分区范围进行查询

SYS@cams> select count(*) from cams_core.bp_voucher_history partition where ac_dte>=to_date('2017-01-01','yyyy-mm-dd') and ac_dte<to_date('2017-02-01','yyyy-mm-dd');

COUNT(*)

----------

2844459

（3）如果 不知道分区的名字，也不知道分区主键的字段值范围 ，可以使用PARTITION FOR子句进行查询，比如现在只知道2017-01-15是这个分区的数据

SYS@cams> select count(*) from cams_core.bp_voucher_history partition for(to_date('2017-01-15','yyyy-mm-dd'));

COUNT(*)

----------

2844459

注：PARTITION FOR子句可以用于指定分区，而不使用分区的名字。

3.根据分区内的分区字段值，查询Interval Partition分区的名字

因为Oracle并没有提供直接的方法用于指定某个日期属于哪个分区，所以这里要借助于dba_tab_partitions的high_value。但是这里又有一个问题，high_value是Long类型的，不能使用to_date或者to_char函数直接进行转化。

所以，要解决根据分区字段值查询分区的问题，本文的解决方案是把Oracle数据库的Long类型转化为varchar2类型或者date类型，然后进行比对，查找出分区的名字。

1 2 3 4 5 6 7 8 9 10 11 12 13	`set` `serveroutput` `on``;` `--/` `declare` `my_var` `date``;` `begin` `for` `x` `in` `(``select` `*` `from` `dba_tab_partitions` `where` `table_owner=``'CAMS_CORE'` `and` `table_name=``'BP_VOUCHER_HISTORY'``) loop` `execute` `immediate` `'select '``\|\| x.high_value \|\|` `'from dual'` `into` `my_var;` `if (my_var = to_date(``'2017-02-01'``,``'yyyy-mm-dd'``))` `then` `dbms_output.put_line(x.partition_name);` `end` `if;` `end` `loop;` `end``;` `/`

Oracle查询Interval partition分区表内数据（转载）_数据_02

同理，对于使用数字进行自动分区的情况，也可以通过类似的方法进行处理。

从MOS上找到的用于将high_value转化为varvhar2类型的方法，这里进行分享（已经对部分参数进行修改）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28	`select` `subname,` `TO_CHAR(y1*100+y2,` `'9999'``) \|\|` `'/'` `\|\|` `TO_CHAR(m,` `'FM09'``) \|\|` `'/'` `\|\|` `TO_CHAR(d,` `'FM09'``) \|\|` `' '` `\|\|` `TO_CHAR(hh,` `'FM09'``) \|\|` `':'` `\|\|` `TO_CHAR(mi,` `'FM09'``) \|\|` `':'` `\|\|` `TO_CHAR(ss,` `'FM09'``)` `from` `(` `SELECT` `o.subname, tp.part#,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 3, 2),` `'XX'``)-100 y1,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 5, 2),` `'XX'``)-100 y2,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 7, 2),` `'XX'``) m,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 9, 2),` `'XX'``) d,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 11, 2),` `'XX'``)-1 hh,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 13, 2),` `'XX'``)-1 mi,` `TO_NUMBER(SUBSTR(RAWTOHEX(``CAST``(bhiboundval` `as` `raw(8))), 15, 2),` `'XX'``)-1 ss` `from` `sys.tabpart$ tp, sys.obj$ o, sys.``user``$ u` `where` `tp.obj# = o.obj#` `and` `o.owner# = u.``user``#` `and` `o.``name` `=` `'BP_VOUCHER_HISTORY'` `and` `u.``name` `=` `'CAMS_CORE'``)` `order` `by` `part#;`

Oracle查询Interval partition分区表内数据（转载）_数据_03

1 2 3 4 5 6 7 8 9	`with` `xml` `as` `(` `select` `dbms_xmlgen.getxmltype(``'select table_name, partition_name, high_value from dba_tab_partitions where table_name = '``'BP_VOUCHER_HISTORY'``' and table_owner='``'CAMS_CORE'``''``)` `as` `x` `from` `dual` `)` `select` `extractValue(rws.object_value,` `'/ROW/TABLE_NAME'``) table_name,` `extractValue(rws.object_value,` `'/ROW/PARTITION_NAME'``) partition,` `extractValue(rws.object_value,` `'/ROW/HIGH_VALUE'``) high_value` `from` `xml x,` `table``(xmlsequence(extract(x.x,` `'/ROWSET/ROW'``))) rws` `ORDER` `BY` `extractValue(rws.object_value,` `'/ROW/TABLE_NAME'``);`

Oracle查询Interval partition分区表内数据（转载）_xml_04

4.附录（MOS原文）

Partition HIGH_VALUE Wrong When NLS_CALENDAR Not GREGORIAN (文档 ID 1964566.1)	转到底部

Oracle查询Interval partition分区表内数据（转载）_oracle_06

In this Document SymptomsChangesCauseSolutionReferences This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review. APPLIES TO: Oracle Database - Enterprise Edition - Version 12.1.0.1 and later Information in this document applies to any platform. SYMPTOMS When the NLS_CALENDAR value in a session is set to non-Gregorian, and a partition is created with a date specifying X(Gregorian), the data dictionary shows Y(Gregorian). This is true when using TO_DATE and DATE, as shown in the following examples: -- Example using TO_DATE ALTER SESSION SET nls_calendar='English Hijrah'; CREATE TABLE t20 (d DATE) PARTITION BY RANGE (d) (PARTITION p1 VALUES LESS THAN (TO_DATE (' 2014-12-31 23:59:59','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))); PARTI HIGH_VALUE ----- ------------------------------------------------------------------------------ P1 TO_DATE(' 1436-03-09 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') -- this should display as 2014-12-31 -- Example using DATE (according to the documentation, DATE should always be Gregorian) ALTER SESSION SET nls_calendar='Gregorian'; CREATE table t (d DATE) PARTITION BY RANGE (d) (PARTITION p0 VALUES LESS THAN (DATE '0001-01-01')); ALTER SESSION SET nls_calendar='Gregorian'; ALTER table t ADD PARTITION p_GREGORIAN VALUES LESS THAN (DATE '2015-01-01'); ALTER SESSION SET nls_calendar='Japanese Imperial'; ALTER table t ADD PARTITION p_JAPANESE_IMPERIAL VALUES LESS THAN (DATE '4003-01-01'); ALTER SESSION SET nls_calendar='Thai Buddha'; ALTER table t ADD PARTITION p_THAI_BUDDHA VALUES LESS THAN (DATE '1472-01-01'); SELECT partition_name, high_value FROM user_tab_partitions where table_name = 'T'; PARTITION_NAME HIGH_VALUE ------------------- -------------------------------------------------------------------------------- P0 TO_DATE(' 0001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P_GREGORIAN TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA P_JAPANESE_IMPERIAL TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA -- this should display as 4003-01-01 P_THAI_BUDDHA TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA -- this should display as 1472-01-01 CHANGES The value of NLS_CALENDAR was set to something other than GREGORIAN in the session. CAUSE The non-binary high-bound value is wrong in the data dictionary when a calendar other than GREGORIAN is specified. This affects the high_value displayed in dba_tab_partitions and the value displayed for the partition DDL pulled via dbms_metadata.get_ddl. The binary high value is correct. Bug:20270851 - PARTITION TEXT HIGH_VALUE IS WRONG WHEN NLS_CALENDAR IS NOT GREGORIAN was filed for this issue. The fix, when available, should include a script or procedure to reset the text values. This bug does NOT affect the insertion of rows into the correct partitions, as the binary high-bound value is used for that. SOLUTION Until Bug:20270851 is fixed, use the workaround to see the correct high-bound values. WORKAROUND: You can obtain the correct text high values from the binary high value column (bhiboundval): select subname, TO_CHAR(y1100+y2, '9999') \|\| '/' \|\| TO_CHAR(m, 'FM09') \|\| '/' \|\| TO_CHAR(d, 'FM09') \|\| ' ' \|\| TO_CHAR(hh, 'FM09') \|\| ':' \|\| TO_CHAR(mi, 'FM09') \|\| ':' \|\| TO_CHAR(ss, 'FM09') from ( SELECT o.subname, tp.part#, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 3, 2), 'XX')-100 y1, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 5, 2), 'XX')-100 y2, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 7, 2), 'XX') m, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 9, 2), 'XX') d, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 11, 2), 'XX')-1 hh, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 13, 2), 'XX')-1 mi, TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 15, 2), 'XX')-1 ss from sys.tabpart$ tp, sys.obj$ o, sys.user$ u where tp.obj# = o.obj# and o.owner# = u.user# and o.name = 'T' and u.name = 'SCOTT') order by part#; Example output: SUBNAME TO_CHAR(Y1100+Y2,'9999') ------------------------------ ------------------------- P0 1/01/01 00:00:00 P_GREGORIAN 2015/01/01 00:00:00 P_THAI_BUDDHA 1472/01/01 00:00:00 P_JAPANESE_IMPERIAL 4003/01/01 00:00:00 REFERENCES BUG:20270851 - PARTITION TEXT HIGH_VALUE IS WRONG WHEN NLS_CALENDAR IS NOT GREGORIAN

How To Select Specific Interval Partition With Sysdate? (文档 ID 2325059.1)	转到底部

Oracle查询Interval partition分区表内数据（转载）_oracle_06

In this Document GoalSolution APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.4 and later Information in this document applies to any platform. GOAL Retrieving interval partition is difficult because it contains long column and there is no other method available to retrieve partition information and so query using XML is an alternative method to retrieve partition information from user_tab_partitions. SOLUTION Query to retrieve high value from user_tab_partitions with xml as ( select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name = ''<Table Name>''') as x from dual ) select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name, extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition, extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value from xml x, table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws ORDER BY extractValue(rws.object_value, '/ROW/HIGH_VALUE');