Re: Interesting new V$ table in 11.2

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 16 Sep 2011 22:44:29 +0000 (UTC)
Message-ID: <pan.2011.09.16.22.44.29_at_gmail.com>

On Fri, 16 Sep 2011 23:28:10 +0200, Maxim Demenko wrote:

> I've seen recorded in the 10046 trace the full text of queries for the
> views which are shortened due to 4000 byte restriction.

You don't need 10046 trace, V$FIXED_VIEW_DEFINITION will show you the create statements.

> However, if i
> remember correctly, only
>
> V$FLASH_RECOVERY_AREA_USAGE
> V$BACKUP_CONTROLFILE_SUMMARY
> V$BACKUP_DATAFILE_SUMMARY
>
> were shortened, the rest doesn't reach the limit.

There are few more than that but the rest indeed are visible.

SQL> select view_name from V$FIXED_VIEW_DEFINITION where length(view_definition)>=3999;

VIEW_NAME

---

GV$SESSION
GV$SQL_SHARED_CURSOR
GV$STREAMS_CAPTURE

V$RECOVERY_AREA_USAGE
GV$ACTIVE_SESSION_HISTORY
GV$WLM_PCMETRIC

V$RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY

GV$IOSTAT_FILE 10 rows selected.

In case of the GV$SESSION view, the from clause isn't visible, so it's impossible to see which tables are being used. Not even the cunning plan like this will reveal the tables:

SQL> select referenced_name from dba_dependencies   2 where name='GV$SESSION';

REFERENCED_NAME

---

GV_$SESSION SQL> select referenced_name from dba_dependencies   2 where name='GV_$SESSION';

REFERENCED_NAME

---

GV$SESSION I also tried re-factoring the V$FIXED_VIEW_DEFINITION view in vain hope that the underlying column might be a CLOB, to no avail:

SQL> select view_definition from V$FIXED_VIEW_DEFINITION   2 where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_DEFINITION

---

select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.in
dx

The "SEL" column in the X$KQFVT is also VARCHAR2(4000), which means that I have no way of extracting the DDL for the 10 views mentioned above.

SQL> desc x$kqfvt
 Name Null? Type

• --------

  ---

  ADDR RAW(4) INDX NUMBER INST_ID NUMBER KQFTPSEL VARCHAR2(4000)

Mark's tip puts me on the right track, because it is possible to get the DDL and the tables for GV$SESSION_WAIT. My cunning plan has failed, which should not come as a big surprise to anyone who has watched "Black Adder".

-- 
http://mgogala.byethost5.com

Received on Fri Sep 16 2011 - 17:44:29 CDT