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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 20 Apr 2011 11:06:24 +0200
Message-ID: <000c01cbff3a$3a6d78b0$af486a10$_at_gmail.com>



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_at_gmail.com] Gesendet: Montag, 18. April 2011 22:22
An: Maxim Demenko
Cc: oracle-l_at_freelists.org
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_at_gmail.com> 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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 20 2011 - 04:06:24 CDT

Original text of this message