Re: How to prevent using shared pool with dynamic sql pivoting?

From: <>
Date: Mon, 16 Jun 2008 19:42:40 +0200
Message-ID: <>

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek <> wrote:

>When I add a new row in emp table with new job title, Oracle is not
>aware of the new column to be displayed until I do the "ALTER SYSTEM
>FLUSH SHARED_POOL" or change some letters in the query. Since this
>query is to be used in several third party tools, this "technique"
>does not seem acceptable to me.

This is very vague description of what you are doing. You would need to post actual statements. Let's assume for a moment you are issuing an ordinary INSERT statement.
Only the session INSERTing will be able to SELECT the data courtesy of Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of understanding how Oracle works, which is COMPLETELY unacceptable, as by flushing the shared pool, you invalidate ALL cursors, and cause ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the door of unemployment.

Sybrand Bakker
Senior Oracle DBA
Received on Mon Jun 16 2008 - 12:42:40 CDT

Original text of this message