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