From: Tornblad, John <>
Date: Thu, 31 Jan 2013 22:02:39 +0000
Message-ID: <>

We have an application with several hundred tables that are partitioned by date (and they are DAY-sized partitions over years = a LOT of partitions). When we want to do various types of partition maintenance operations (TRUNCATE, DROP, SPLIT) it seems our shared pool is being abused by some recursive SQL that is generated during a PMOP:

For every:

We see one of these get executed:
  LOCK TABLE ... PARTITION (...) IN EXCLUSIVE MODE NOWAIT If we want to TRUNCATE, DROP, MOVE or SPLIT some partitions, we have thousands of these LOCK TABLE cursors piling up [no ALTER TABLE cursors though... those seem to vanish into the ether]. "Cursor leak"? It's so bad we've eventually received errors (ORA-04031: unable to allocate 32 bytes of shared memory) regarding being unable to expand the shared pool any further.

I understand the need for the LOCK TABLE statements, I just don't understand why they are piling up and flooding the shared pool. Their status is "INVALID_UNAUTH" which I suppose means that they are no longer valid, but they seem to be hanging around, not being reaped or purged and if we execute too many PMOPs (or perhaps too quickly?), we have problems. This appears to be adding up to many GBs of memory allocated to the shared pool, and we cannot sustain that for very long.

Wondering how to deal with "large" numbers of partitions, when you need to perform a large number of PMOPs.

Observed in,


</pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.
-- Received on Thu Jan 31 2013 - 23:02:39 CET

Original text of this message