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 10.2.0.4 / Linux 32 I get only sql for V$BACKUP_DATAFILE_SUMMARY (
from the mentioned above 3 views with content exceeding 4000 char),

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

V$FLASH_RECOVERY_AREA_USAGE

V$BACKUP_CONTROLFILE_SUMMARY

V$BACKUP_DATAFILE_SUMMARY

) recorded, however still nothing for v$session.

 

Maybe someone else can shed more light on this inconsistent behaviour

 

Best regards

 

Maxim

 

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
command.

 

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).

Thanks,

Paresh

 

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 freelists.org). 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.
 
Paresh

 

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

On 15.04.2011 19:26, Paresh Yadav wrote: 

Hi, 

 

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)


-- 
Thanks
Paresh



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

V$FLASH_RECOVERY_AREA_USAGE
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_DATAFILE_SUMMARY
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#,  <http://ts.na> ts.na me
                          from v$datafile df, v$tablespace ts
                         where ts.ts# = df.ts#) b
                 where a.file# = b.file#(+))) a

Best regards

Maxim





-- 
Thanks
Paresh
416-688-1003




-- 
Thanks
Paresh
416-688-1003

References:
- Complete (more than 4000 char) text of fixed view definition?
  - From: Paresh Yadav

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

Other related posts: