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

本文对于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';

注：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

  1. 注：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;
/

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

 

从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#;

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');

 

4.附录（MOS原文）

 

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

 

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(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 = 'T' and u.name = 'SCOTT') 
order by part#;


Example output:

SUBNAME                        TO_CHAR(Y1*100+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) 转到底部 转到底部

 

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');

 

 

 

各位读者朋友如果有问题、有想法、有意见，可以在文末留言，也可关注作者微信公众号“IT技术佳肴”，与作者交流。

 posted on 2020-10-29 14:06  xibuhaohao  阅读(1622)  评论(0)  编辑  收藏  举报