Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say:

select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian')
  from my_table;

I created a table as:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20))
partition by range(xdate)
(partition p0 values less than (to_date('13920101', 'YYYYMMDD', 'nls_calendar=persian')))
enable row movement;

the table is created normally, but when I add records to it, and oracle creates new partitions, the partitions are:

create table TEMP_TIMES (
  id    NUMBER(18) not null,
  xdate DATE not null,
  str   VARCHAR2(20)
partition by range (XDATE)
  partition P0 values less than (TO_DATE(' 2013-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P61 values less than (TO_DATE(' 2013-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P62 values less than (TO_DATE(' 2013-05-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P63 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P64 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

as you can see, the database changed the NLS_CALENDAR to gregorian (the same day as 13920101) and each partition is created according to gregorian calendar, not persian calendar.

Is there any way to force Oracle to use persian calendars for creating new partitions?

share|improve this question
You are misunderstanding - Oracle is working correctly - it has converted the dates to the database NLS settings when creating the partitions. When you insert persian dates using the correct NLS settings they will be stored correctly, & the same goes for selecting. As long as the the partitions align with the persian calendar, what is the problem? – Phil Jan 5 '14 at 19:04
Well partitions are not aligned with Persian calendar, for example 2013-03-21, 2013-04-21, 2013-05-22, 2013-06-22, 2013-07-23, 2013-08-23, 2013-09-23, 2013-10-23, 2013-11-22, 2013-12-22... are first dates of Persian calendar months, as you must have noticed some are 21th of gregorian month, some are 22th, some are 23th and even some are 20th! – Amir Pashazadeh Jan 5 '14 at 20:24
My apologies then. I hadn't read the question thoroughly enough & done enough date conversions. I've just done a read up of how the calendar works. What're you using it for? – Phil Jan 5 '14 at 23:12
Well in my original case each partition is get loaded with millions or records, and all the reports are from a single Persian (Jalali) month, If i could align the partitions to Persian calendar, the query needs to scan a single partition, otherwise two or sometimes 3 partitions are needed to be scanned. – Amir Pashazadeh Jan 6 '14 at 3:57
Intervals are sensitive to NLS_CALENDAR (note 227334.1), but for table/partition definitions I guess it would have to be the database setting - the session level doesn't apply, and you can't override it in the interval itself. Assuming the DB parameter is Gregorian, it doesn't look like you can do this with intervals, and will have to manually define all the partitions? Another approach, I guess, would be to add a virtual column with a numeric representation of the month and partition on that? – Alex Poole Jan 6 '14 at 11:48
up vote 5 down vote accepted

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (Persian) month each date falls in, using a virtual column:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20),
  ydate as (to_number(to_char(xdate, 'YYYYMM', 'nls_calendar=persian')))
partition by range(ydate)
(partition p0 values less than (139201))
enable row movement;

If that is populated with a record for every day for the year following your example start date:

insert into test_temp_times (id, xdate, str)
select level, date '2013-03-20' + level, null
from dual
connect by level < 366;

The partitions that are created will look something like:

select table_name, partition_name, high_value
from user_tab_partitions where table_name = 'TEST_TEMP_TIMES';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_TEMP_TIMES                P0                             139201     
TEST_TEMP_TIMES                SYS_P479                       139202     
TEST_TEMP_TIMES                SYS_P480                       139203     
TEST_TEMP_TIMES                SYS_P481                       139204     
TEST_TEMP_TIMES                SYS_P482                       139205     
TEST_TEMP_TIMES                SYS_P483                       139206     
TEST_TEMP_TIMES                SYS_P484                       139207     
TEST_TEMP_TIMES                SYS_P485                       139208     
TEST_TEMP_TIMES                SYS_P486                       139209     
TEST_TEMP_TIMES                SYS_P487                       139210     
TEST_TEMP_TIMES                SYS_P488                       139211     
TEST_TEMP_TIMES                SYS_P489                       139212     
TEST_TEMP_TIMES                SYS_P490                       139213     

 13 rows selected 

And you can check which partitions the month boundaries fall in:

select utp.partition_name, min(ttt.xdate), max(ttt.xdate)
from test_temp_times ttt
join user_objects uo on uo.object_id = dbms_rowid.rowid_object(ttt.rowid)
join user_tab_partitions utp on utp.table_name = uo.object_name
and utp.partition_name = uo.subobject_name
group by utp.partition_name
order by partition_name;

------------------------------ -------------- --------------
P0                             2013-03-20     2013-03-20     
SYS_P479                       2013-03-21     2013-04-20     
SYS_P480                       2013-04-21     2013-05-21     
SYS_P481                       2013-05-22     2013-06-21     
SYS_P482                       2013-06-22     2013-07-22     
SYS_P483                       2013-07-23     2013-08-22     
SYS_P484                       2013-08-23     2013-09-22     
SYS_P485                       2013-09-23     2013-10-22     
SYS_P486                       2013-10-23     2013-11-21     
SYS_P487                       2013-11-22     2013-12-21     
SYS_P488                       2013-12-22     2014-01-20     
SYS_P489                       2014-01-21     2014-02-19     
SYS_P490                       2014-02-20     2014-03-19

At least, I think that's what you're trying to achieve... Unfortunately I can't add a demo as SQL Fiddle doesn't have the partitioning option, but this is tested against

Of course, you have to make it use the partitions for the query... if I just do:

select * from test_temp_times
where xdate = date '2013-11-01';

It finds the row with plan:

| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT    |                 |     1 |    47 |   164   (0)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|                 |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TEST_TEMP_TIMES |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|

If I explicitly add the virtual column to the query:

select * from test_temp_times
where xdate = date '2013-11-01'
and ydate = to_number(to_char(date '2013-11-01', 'YYYYMM', 'nls_calendar=persian'));

Then it knows which partition to query:

| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT       |                 |     1 |    47 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS FULL    | TEST_TEMP_TIMES |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |

Clearly I haven't created any indexes yet. If you are looking for a whole month's worth of data, you would only need to query on a single ydate value, and ignore xdate; but presumably you'd need a mix at least some of the time.

share|improve this answer
Thanks for complete answer, by the way I now know how to achieve this, but I can not query like that, so I created partitions by range and will remember to add more partitions each months. – Amir Pashazadeh Jan 6 '14 at 16:23
@AmirPashazadeh - you could probably schedule the partition creation; but again only on Gregorian-monthly intervals... – Alex Poole Jan 6 '14 at 16:30
What about creating a local index on xdate then? Does analyzing that index help? So Oracle determine what are max and min values of xdate in each partition, so then scans the only proper partition? – Amir Pashazadeh Jan 6 '14 at 17:50
@AmirPashazadeh - with a local index on xdate the plan still has PARTITION RANGE ALL; but the index would mean it's only actually retrieving data from one partition, since there is no table scan. With a lot of data though the index might not help much, and could slow things down, or be ignored; depends on the distribution. Adding the virtual column to the query is still the only way to help in this case I think. – Alex Poole Jan 6 '14 at 18:09

Salam! You said to oracle to use an interval of 30 days, so it adds that interval to sysdate and uses that! I think instead of numtodsinterval(1, 'MONTH') you better use numtodsinterval(month_days(column_name), 'DAY') which month_days calculates number of persian month days like this:


        WHEN 1
            RETURN 31;
share|improve this answer
Well, it's a month, not 30 days; but the point is that it's always using the Gregorian equivalent of the Persian date in the P0 partition to calculate the intervals for future partitions. So the automatic interval partitions don't align with the Persian calendar. – Alex Poole Jan 6 '14 at 10:10
I'm not sure whether I can use this function for intervals or not, but that could help because first 6 months of Jalali calendar are 31 days, 5 more months are 30 days, and last month is 29 or 30 days (for leap years). – Amir Pashazadeh Jan 6 '14 at 17:52

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.