How intermittently are you getting the error?
Do you have any logging in the trigger that is truncating a partition? You could use a simple procedure that writes (in an autonomous transaction) some information about which partition is being truncated.
Also create the on servererror trigger as suggested by Leigh Riffel, and log the query and error stack.
Finally, I would enable tracing of all occurrences of the long running query (assuming this query isn't executed all too often!):
alter session set sql_trace = true;
alter session set tracefile_identifier = wrschneider08103;
before the query and
alter session set sql_trace = false;
after.
Then, once the error has again reared its ugly head, run tkprof on the last trace file with 'wrschneider08103' in its name...
Hopefully you'll be able to correlate the information you've thus collected.
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 < 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.
Best Answer
I would write a job procedure which would get all running queries:
and then loop through that cursor and get explain plan