oracle查看多个分区,Oracle查询Interval partition分区表内数据

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.

- 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

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

- PARTITION TEXT HIGH_VALUE IS WRONG WHEN NLS_CALENDAR IS NOT GREGORIAN