AW: Complete (more than 4000 char) text of fixed view definition?

  • From: "Maxim Demenko" <mdemenko@xxxxxxxxx>
  • To: "'Paresh Yadav'" <yparesh@xxxxxxxxx>
  • Date: Wed, 20 Apr 2011 11:06:24 +0200

I stand corrected, indeed, v$session underlying recursive sql won't get
recorded, hovewer, it seems, this behaviour vary depending on particular
fixed view, oracle version a/o platform.


So, on / Linux 32 I get only sql for V$BACKUP_DATAFILE_SUMMARY (
from the mentioned above 3 views with content exceeding 4000 char),

On the / Solaris x86 I get however all 3 views sql (




) recorded, however still nothing for v$session.


Maybe someone else can shed more light on this inconsistent behaviour


Best regards




Von: Paresh Yadav [mailto:yparesh@xxxxxxxxx] 
Gesendet: Montag, 18. April 2011 22:22
An: Maxim Demenko
Cc: oracle-l@xxxxxxxxxxxxx
Betreff: Re: Complete (more than 4000 char) text of fixed view definition?


strings $ORACLE_HOME/bin/oracle is not very helpful as it is hard to piece
the information together (assuming the fixed view definitions are there
which I believe is not there from what I saw in the results of strings


I did a 10046 trace (level 12) and ran "select * from v$session".
Unfortunately the underlying recursive SQL against x$ tables is not there in
the resulting trace file. A side observation is that the recursive SQL is
NOT logged in the trace file if logged in as SYS (Not sure if there is any
setting to enable/disable this behaviour).




On Sat, Apr 16, 2011 at 3:36 PM, Paresh Yadav <yparesh@xxxxxxxxx> wrote:

Thanks Stephane for pointing me to strings idea (BTW - you posting didn't
arrive in my inbox through the mailing list!!, I found it while browsing the
thread on Tried strings (from sysinternals) on windows but
couldn't get far as 1) Long lines can't be handled by strings on windows and
2) I think it will be difficult to connect together different lines with
v$session and x$ table (they should be consecutive lines though). Will try
again on Linux on Monday and report back.
Thanks Maxim for 10046 trace suggestion. I think this should do the trick.
Will check on Monday.


On Sat, Apr 16, 2011 at 5:16 AM, Maxim Demenko <mdemenko@xxxxxxxxx> wrote:

On 15.04.2011 19:26, Paresh Yadav wrote: 



How can I get complete (more than 4000 char) text of fixed view definition?

SQL> desc v$fixed_view_definition


Name                                      Null?    Type

 ----------------------------------------- -------- -----------------------


 VIEW_NAME                                          VARCHAR2(30)

 VIEW_DEFINITION                                    VARCHAR2(4000)


For the most of them definition is not chomped - on my db there are
for example only

which exceed the 4000 char limit.

But of course, anything you can query, can be revealed too - 
this is what i see in the 10046 trace for V$BACKUP_DATAFILE_SUMMARY:

. snipped to shorten

                         group by a.file#, creation_change#) a
                      ,(select df.file#, df.ts#,  <> me
                          from v$datafile df, v$tablespace ts
                         where ts.ts# = df.ts#) b
                 where a.file# = b.file#(+))) a

Best regards




Other related posts:

  • » AW: Complete (more than 4000 char) text of fixed view definition? - Maxim Demenko