Where is the rest of Oracle's V$ view definitions?

Question

The view V$FIXED_VIEW_DEFINITION contains the definition of all V$ views. However, the datatype is VARCHAR2(4000), so only the first 4000 characters of the view's body are displayed:

SELECT column_name, data_type, data_length 
  FROM dba_tab_cols 
 WHERE table_name = 'V_$FIXED_VIEW_DEFINITION';

COLUMN_NAME      DATA_TYPE  DATA_LENGTH
---------------  ---------  -----------
VIEW_NAME        VARCHAR2           128
VIEW_DEFINITION  VARCHAR2          4000
CON_ID           NUMBER              22

This works fine for views which are shorter, but for others, like V$SESSION, the query text stops right after character 4000:

SELECT * FROM v$fixed_view_definition WHERE view_name = 'GV$SESSION';

VIEW_NAME   VIEW_DEFINITION
----------  --------------------------------------------
GV$SESSION  select s.inst_id, ... ,decode(bitand(s.ksuse
                                 here it stops abruptly ^

Now, obviously, Oracle has stored the full text somewhere, and the function DBMS_UTILITY. EXPAND_SQL_TEXT T reconstructs the full query, but not without doing strange things to the query like quoting all columns, introducing alias etc:

DECLARE
  c CLOB;
  i NUMBER := 1;
  linelen CONSTANT NUMBER := 100;
BEGIN
  DBMS_UTILITY.EXPAND_SQL_TEXT('SELECT * FROM V$SESSION', c);
  LOOP 
    EXIT WHEN i > DBMS_LOB.GETLENGTH(c);
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(c, linelen, i));
    i := i + linelen;
  END LOOP;
END; 
/


SELECT "A1"."SADDR" "SADDR","A1"."SID" "SID","A1"."SERIAL#" "SERIAL#","A1"."AUDS
ID" "AUDSID","A1"."PADDR" "PADDR","A1"."USER#" "USER#","A1"."USERNAME" "USERNAME
...

So, where is the rest of the V$ query bodies?

user_3061852 · Answer

For the sake of having a clean answer, it sounds like you discovered the full fixed view source queries compiled into the Oracle binary by using

strings $ORACLE_HOME/bin/oracle

and grepping the output for a specific view.

Burleson had provided a hint that the fixed view synonyms are created by the $ORACLE_HOME/rdbms/admin/ catalog scripts (specifically cdfixed.sql).

user_11227919 · Answer

try this,

spool C:\Users	est\Downloads\VW_DDL.txt
SET LONG 200000 LONGCHUNKSIZE 500000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, 'SYSADM')
FROM   all_views
WHERE  owner      = 'SYSADM'
AND    view_name IN (' ');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
spool off

Where is the rest of Oracle's V$ view definitions?

2 Answers