AW: Complete (more than 4000 char) text of fixed view definition?
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-lReceived on Wed Apr 20 2011 - 04:06:24 CDT