Oracle 如何根据波斯月份格式对表进行分区？

你好，亲爱的汤姆
如您所知，oracle 12c支持伊朗领土。

col PARAMETER format a30;
col VALUE format a30;
select * from v$nls_valid_values where lower(VALUE) in ('iran','persian');
PARAMETER                      VALUE                          ISDEP     CON_ID
------------------------------ ------------------------------ ----- ----------
LANGUAGE                       PERSIAN                        FALSE          0
TERRITORY                      IRAN                           FALSE          0

我打算创建一个基于波斯语月份间隔的分区表，DDL如下:

create table 
pos_data ( 
   start_date DATE
) 
PARTITION BY RANGE (start_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
);
Table created.

我在表中插入1000行

begin

  for i in 0..1000 loop
    insert into pos_data values (sysdate + i);
  end loop;
  commit;
  
end;
/

当我选择high_value时，我希望输出为nls_calndar=persian但实际产出是:

select high_value from USER_TAB_PARTITIONS;
TO_DATE(' 1395-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-06-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2017-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
.
.
.

当前的nls_database_parameters和nls_instance_parameters设置为:

select * from NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION              12.2.0.1.0
NLS_NCHAR_CONV_EXCP            FALSE
NLS_LENGTH_SEMANTICS           BYTE
NLS_COMP                       BINARY
NLS_DUAL_CURRENCY              $
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_SORT                       BINARY
NLS_DATE_LANGUAGE              AMERICAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_CALENDAR                   GREGORIAN
NLS_NUMERIC_CHARACTERS         .,
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               AL32UTF8
NLS_ISO_CURRENCY               AMERICA
NLS_CURRENCY                   $
NLS_TERRITORY                  AMERICA
NLS_LANGUAGE                   AMERICAN
20 rows selected.

select * from NLS_instance_parameters;
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  IRAN
NLS_SORT
NLS_DATE_LANGUAGE              PERSIAN
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR                   PERSIAN
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
17 rows selected.

我想知道，问题在哪里？
致以最诚挚的问候，
马苏德

关键是你的NLS_DATABASE_PARAMETERS

NLS_CALENDAR                   GREGORIAN

这是 * 创建 * 数据库时设置的日历。

您的实例级参数将始终覆盖这一点，但是由于您可以 * 更改 * 实例参数，因此内部字典结构似乎将存储/表示它们在基本数据库日历中的值，以便它们始终保持一致。

据我所知 (即，我的波斯语日历知识不是很好)，定义看起来正确，间隔与否

SQL> create table pos_data (
  2     start_date DATE
  3  )
  4  PARTITION BY RANGE (start_date)
  5  (
  6     PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
  7  );

Table created.

SQL>
SQL> col high_value format a90
SQL> set lines 200
SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions where table_name = 'POS_DATA';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2                    TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>
SQL> drop table pos_Data purge;

Table dropped.

SQL>
SQL> create table pos_data (
  2     start_date DATE
  3  )
  4  PARTITION BY RANGE (start_date)
  5  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  6  (
  7     PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-12-1395', 'DD-MM-YYYY', 'nls_calendar=persian'))
  8  );

Table created.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions where table_name = 'POS_DATA';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------
POS_DATA_P2                    TO_DATE(' 2017-02-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>