Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stan.

Asked: December 17, 2015 - 1:29 pm UTC

Last updated: December 18, 2015 - 2:48 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom

Looking for your advice on what can be done to minimize probability of hitting ORA-12842 error for user reports that are running parallel queries against a table that is subject to concurrently running uploads using "exchange partition" technique.
Our business requirements prohibit us from restricting uploads to only "maintenance window" and users are expected to run their reports as/when they wish. What bothers us the most, is that such error is being generated even when partition exchange is done against different partition that is being queried ( current vs historical). Currently we have a table that is range-partitioned by date. Oracle version 11.2.0.4.
Any tricks or techniques, including significant changes to the design, that can reduce a chance for that error?

Thank you
Stan

and Connor said...

Well MOS note 1322894.1 describes a method for suppressing the error, but I would think *very* carefully before heading down that path. The workaround in that note I'm pretty sure was designed the case where you *know* that the consistency of the data has not been changed with the change in object definition (for example, adding an empty partition)

If you are exchanging partitions, I'm assuming you are doing so because you have altered data. But take a look at the note.

Other things to consider

1) Dynamically minimise collision chances

When your exchange is about to occur, query v$session for active sessions, use the SQL_ID to drive into V$SQLSTATS and probe if the relevant table(s) are referenced in the active query. If so, pause, and try again in 3 seconds etc etc...ultimately after (say) 60 seconds deciding whether to abandon the exchange or just go ahead and do it anyway.

2) Minimise exchange time

Do all validation outside the exchange process, so you can exchange using "without validation" etc. Combined with (1), you are basically trying to serialize the operations as best as possible without actually blocking anyone.

3) Views for segmentation

(I havent tested this one, so cant be sure that it actually helps)

If you know that most queries are for (say) the last 12 months and you are doing exchanges on partitions older than that, then create a view which is the union all of those partitions for the last 12 months, and allow users to query that. (I'm saying union all because my hypothesis is that would be more insulated from the error than a view which was just on the whole table with a date predicate).
`

Is this answer out of date? If it is, please let us know via a Comment