Thanks for the question, Slavcho.
Asked: April 03, 2017 - 2:20 pm UTC
Last updated: April 04, 2017 - 1:15 pm UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi,
I have 4 tables in 4 schemas with monthly interval partitioning:
Schemas: C, D, F, G
Tables: T1, T2, T3, T4
So, I have these 4 tables in each schema (C, D, F, G).
On April 1st, Oracle failed to create partitions (April) for same table (table_name=T1) in all schemas. Partitions were created for all other tables (T2, T3, T4) in all schemas.
There is no error in alert.log.
There are trace files with this in it:
"KQRCMT: Write failed with error=604 po=0x13e8cb8988 cid=8"
Error on application side is:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
There are multiple entries for added partitions in alert log:
TABLE D.T1: ADDED INTERVAL PARTITION SYS_P30981 (23) VALUES LESS THAN (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE D.T1: ADDED INTERVAL PARTITION SYS_P30995 (23) VALUES LESS THAN (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE D.T1: ADDED INTERVAL PARTITION SYS_P31009 (23) VALUES LESS THAN (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE D.T1: ADDED INTERVAL PARTITION SYS_P31023 (23) VALUES LESS THAN (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
but none of these partitions were actually created.
At that moment I manually inserted dummy records just to force creation of partitions for the "failed" tables.
Do you have any similar experience or maybe have more information about this?
and Connor said...
If you've got those entries in the alert log and yet those partitions do not actually exist in the dictionary, then that sounds ... well... worrying :-)
I would check to see if you have any ddl triggers that are firing during these events - ddl triggers failing are a potential cause of ddl's not working when they should.
If you dont have any triggers (dont forget, the database itself might have some even if *you* havent created them), then I think its time to get in touch with Support. They'll probably ask you to set an event to get a complete call stack to pursue it further.
Hope this helps. Let us know how you go.
Is this answer out of date? If it is, please let us know via a Comment