Oracle partition by interval using persian calendar
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)
interval(1)
(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 &lt; 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;

PARTITION_NAME                 MIN(TTT.XDATE) MAX(TTT.XDATE)
------------------------------ -------------- --------------
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 11.2.0.3.

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.