RE: missing part of sql statement in v$sql

Thanks, the client isn’t interested in applying the patch since it states that it resolves issue from PSU 6 which they don’t have installed. I doubt they allow me to dump the LC but will ask.

Ken

-----Original Message-----
From: Yong Huang [mailto:yong321_at_yahoo.com] Sent: Monday, September 12, 2016 12:01 PM To: kennethnaim_at_gmail.com; oracle-l_at_freelists.org Cc: christopherdtaylor1994_at_gmail.com
Subject: Re: missing part of sql statement in v$sql

Ken,

As Chris said, Oracle note 1922923.1 explains what happens. Basically, it's a bug where you can resolve by applying a patch or upgrading to 12.1.0.2.

For now, if you have to see the SQL for "create table", you may try dumping library cache at level 4 (or above):

alter session set events 'immediate trace name library_cache level 4';

Be aware that this will cause a hang during the few seconds of doing it, and the "create table" SQL may still be masked. Try it on a non-production database with a small shared pool.

My guess:
I suspect the problem is caused by an over-correction of a security bug. Beginning with early Oracle 9i, SQLs with security implications such as "create user", "alter user" and "grant" are truncated to the first 20 characters (probably related to Bug 208503). But the rule was not consistently enforced when new views were introducted, such as v$sqlstats (Ref: SR 3-7691236071 "Security enhancement request: V$sqlstats shows password for alter user or grant identified by"). On an unpatched database of versions 11.2.0.2 through 12.1.0.1, v$sqlstats shows the password in SQLs like "create user". One of the routine quarterly patches has plugged this hole. But the fix probably "overflows" into "create table" SQLs. Again, this is only a guess.

Yong Huang

• Original Message -------

THere's a bug in 11.2.0.4 with missing SQL_TEXT - you may be hitting it (and apparently several related bugs) V$SQL.SQL_FULLTEXT For Create Table Statement Is Trimmed (Doc ID 1922923.1)

On Fri, Sep 9, 2016 at 1:49 PM, Ken Naim <kennethnaim_at_gmail.com> wrote:

> Sql_fulltext is missing the 10-20 lines, sql_text has only the first
> 20 or so characters. It’s so odd that the middle of a statement would be mis

--
http://www.freelists.org/webpage/oracle-l