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@xxxxxxxxx] ;
Sent: Monday, September 12, 2016 12:01 PM
To: kennethnaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc: christopherdtaylor1994@xxxxxxxxx
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@xxxxxxxxx> 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
