In this Document

APPLIES TO:

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

In this Document

APPLIES TO:

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