We ran into this… but there was more than one bug in the mix.
Bug 17982832 prevented the SQL from being captured correctly in V$SQL
Bug 20308798 prevented the SQL from being captured correctly in AWR (from V$SQL)
So, two 'links in the chain' were broken: getting the full SQL into V$SQL, and
getting the full SQL from V$SQL to DBA_HIST_SQLTEXT.
The SQL was captured accurately in the audit trail (at least, to the limit of
the NVARCHAR2(2000) column SQL_TEXT
Unfortunately, we reported the SQL was affected in AWR – and Oracle Support
recommended only 20308798. But that did not fix our problem – because it was
already chopped in V$SQL. We found Bug 17982832 by our own research.
And then we ran into the hurdle of ‘you need a special password to access patch
Bug 17982832’ – grrrr….
Mike
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Ken Naim
Sent: Saturday, September 10, 2016 10:52 PM
To: 'Mark W. Farnham' <mwf@xxxxxxxx>; 'Beckstrom Jeffrey'
<JBECKSTROM@xxxxxxxxx>; christopherdtaylor1994@xxxxxxxxx
Cc: 'oracle-l-freelists' <oracle-l@xxxxxxxxxxxxx>; rjanuary@xxxxxxxxx
Subject: RE: missing part of sql statement in v$sql
Thank you for the suggestions, unfortunately this code is generated by an
application, hence my need to see the sql in order to tune it. I have this
version of the application running at other clients that do not experience this
issue. Unfortunately I don’t have dba or server level access at this client yet
so I can’t even try the patch that was suggested. I will dump the sql_fulltext
column to see if there are any strange unprintable chatacters.
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Saturday, September 10, 2016 11:26 AM
To: kennethnaim@xxxxxxxxx<mailto:kennethnaim@xxxxxxxxx>; 'Beckstrom Jeffrey'
<JBECKSTROM@xxxxxxxxx<mailto:JBECKSTROM@xxxxxxxxx>>;
christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>
Cc: 'oracle-l-freelists'
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>;
rjanuary@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>
Subject: RE: missing part of sql statement in v$sql
The suspicion arises that somehow you have unprintable characters in the
original source string or some such and corresponding rubout characters.
If possible, view your source via some editor that shows a visible encoding of
unprintables, or failing that carefully manipulate your input sql string.
When (if) all that fails, I would resort to inserting
--< comment line>
between each source text line of the text, the entire thing by hand, if need be.
Something very fishy is afoot.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim
Sent: Saturday, September 10, 2016 12:21 AM
To: 'Beckstrom Jeffrey';
christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>
Cc: 'oracle-l-freelists'; rjanuary@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>
Subject: RE: missing part of sql statement in v$sql
Same, as v$sql.
From: Beckstrom Jeffrey [mailto:JBECKSTROM@xxxxxxxxx]
Sent: Friday, September 9, 2016 3:30 PM
To: christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>;
kennethnaim@xxxxxxxxx<mailto:kennethnaim@xxxxxxxxx>
Cc: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>;
rjanuary@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>
Subject: RE: missing part of sql statement in v$sql
What about v$sqltext?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
Thank you, I searched on metalink and couldn’t find the right keywords for"Ken Naim" <kennethnaim@xxxxxxxxx<mailto:kennethnaim@xxxxxxxxx>> 9/9/16
3:29 PM >>>