Oracle … as usual

Oracle by Laurent Leturgez

Monthly Archives: September 2010

Disassembling V$ views

If you are instesting by Oracle Internals, maybe you have already tried to use the DBMS_METADATA Package to get the definition code of an Oracle static view :

SQL> set long 5000
SQL> set pages 5000
SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_DATA_FILES')
---------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "F
ILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO
", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BY
TES", "USER_BLOCKS", "ONLINE_STATUS") AS
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
.../...


But if you try to get the definition code of the “V$” dynamic views, you will obtain a strange output which mentioned a call to V$DATAFILE (at the end)

SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE')
----------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION
_CHANGE#", "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHE
CKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOV
ERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_
CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZ
E", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME", "FIRST_NONLOGGE
D_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CREATION_CHAN
GE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","ST
ATUS","ENABLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_
CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHAN
GE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS","CREATE_BYTES","B
LOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME","FIRST_NONLO
GGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","FOREIGN_CREATION_CHAN
GE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHANGE#","PLUG
IN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

In fact, dynamic performance view definition are stored in the V$FIXED_VIEW_DEFINITION.
As in 10g and 11g, the V$ views are results of GV$ view, if you want to get the definition view of the V$DATAFILE, a better way is to query this view with the GV$DATAFILE definition.

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='V$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STAT
US , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHA
NGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# ,
ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_
SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SC
N, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREI
GN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_C
HANGE#, PLUGIN_RESETLOGS_TIME from GV$DATAFILE where inst_id = USERENV
('Instance')
SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='GV$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_t
im,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn,
decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), d
ecode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(f
e.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED'
,4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_da
te(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number
(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Grego
rian'), to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL), to_d
ate(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_numb
er(fe.feofs),to_number(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR H
H24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe
csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(fe.fepax,
0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunr
ecscn), to_date(fh.fhfirstunrectime,'
MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, f
e.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.
feprlt from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where
((fe.fe
pax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or
((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4 and fnaux.fnnam is not null and
bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil an
d fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f
nnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum

Now you know that GV$DATAFILE view is using x$kccfe, x$kccfn, x$kcvfh fixed tables.