Re: ALTER TABLE ... DROP or TRUNCATE PARTITION leading to *many* LOCK TABLE cursors

From: Gunawan Yuwono <>
Date: Thu, 31 Jan 2013 21:41:50 -0600
Message-ID: <>

Check Patch 14600407. It might or might not help in your case, but I think it's worth to take a look. Oracle created this patch for us, we were having a lot of issues on a couple of tables w/ 100,000+ subpartitions when doing subpartition adds/exchanges. The patch definitely helps a lot in our case. Thanks,

On Thu, Jan 31, 2013 at 4:02 PM, Tornblad, John <>wrote:

> 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:
> 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,
> -john
> </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.
> --

Only the wisest and the stupidest of men never change - Confucius

Received on Fri Feb 01 2013 - 04:41:50 CET

Original text of this message