A User Group delegate asked me recently if I ever post topics for new Oracle DBAs who may not yet be administering any advanced options such as RAC, ASM, Exadata etc.. Then I had a question during an Oracle Live Virtual Class (LVC) which I presented late last year also from a new DBA who wished to know if there is a quick way to examine all the possible values for certain status columns in Data Dictionary Views or in Dynamic Performance (V$ and GV$) views.
Many of these views contain various columns some of which are of datatype date, some are number, occasionally there are long or clob columns and for the V$ views there are raw columns representing pointers in from one memory structure to another. But there are many important columns that are varchar or maybe char and whose values are important in determining the current situation with regard to the resource in question.
Whilst the Oracle Database Reference contains information on Data Dictionary views in Part II (sections 2 through 6) and Dynamic Performance views in Part III (sections 7 through 9) and contains the various possible values and their meanings one must still find the documentation and navigate to the appropriate view. This may occur when one is creating a filter for example typically “WHERE <COLUMN_NAME> <operator> <VALUE>” and wish to know the literal for “VALUE>”.
I have often encountered this when querying a view that I seldom use and altough I may know that there is a specific status in a column or a view, I may not remember the value or what other possible values are.
Lets start by using the Static Data Dictionary View “DBA_TABLESPACES“ as an example. The columns “STATUS”, “LOGGING”, “FORCE_LOGGING” and all the other columns of “varchar2″ data type contain two or more possible values which may be used in the select list or the where clause or both in a DBA query.
Data Dictionary views are stand-alone queries or join queries that access the underlying base tables of the data dictionary and which occasionally join to dynamic performance views as well. Those objects contain metadata in encoded columns and these are decoded by the views. By querying the text of the views, one may see not only the underlying tables and views that are accessed but also the decode functions which contain all the possible values for the intersting status columns.
One may use the following script to access the text of Data Dictionary Views:
set long 20000
col text for a100
col view_name for a20
undefine viewname
select view_name,text from dba_views
where view_name like upper (‘%&&viewname%’);
undefine viewname
Lets see what we get when looking at DBA_TABLESPACES:
SYS@tutor5d_nledu02 SQL> @sviewtext
SYS@tutor5d_nledu02 SQL> set long 20000
SYS@tutor5d_nledu02 SQL> col text for a100
SYS@tutor5d_nledu02 SQL> col view_name for a20
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL> select view_name,text from dba_views
2 where view_name like upper (‘%&&viewname%’);
Enter value for viewname: DBA_TABLESPACES
old 2: where view_name like upper (‘%&&viewname%’)
new 2: where view_name like upper (‘%DBA_TABLESPACES%’)
Hit Any Key to Continue
VIEW_NAME
——————–
TEXT
——————————————————————————-
DBA_TABLESPACES
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, ‘ONLINE’, 2, ‘OFFLINE’,
4, ‘READ ONLY’, ‘UNDEFINED’),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, ‘UNDO’,
‘PERMANENT’)), 1, ‘TEMPORARY’),
decode(bitand(ts.dflogging, 1), 0, ‘NOLOGGING’, 1, ‘LOGGING’),
decode(bitand(ts.dflogging, 2), 0, ‘NO’, 2, ‘YES’),
decode(ts.bitmapped, 0, ‘DICTIONARY’, ‘LOCAL’),
decode(bitand(ts.flags, 3), 0, ‘USER’, 1, ‘SYSTEM’, 2, ‘UNIFORM’,
‘UNDEFINED’),
decode(ts.plugged, 0, ‘NO’, ‘YES’),
decode(bitand(ts.flags,32), 32,’AUTO’, ‘MANUAL’),
decode(bitand(ts.flags,64), 64,’ENABLED’, ‘DISABLED’),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
‘GUARANTEE’, ‘NOGUARANTEE’)), ‘NOT APPLY’),
decode(bitand(ts.flags,256), 256, ‘YES’, ‘NO’),
decode(tsattr.storattr, 1, ‘STORAGE’, ‘HOST’),
decode(bitand(ts.flags,16384), 16384, ‘YES’, ‘NO’),
decode(bitand(ts.flags,64), 0, null,
decode(bitand(ts.flags,65536), 65536,’FOR ALL OPERATIONS’,
‘DIRECT LOAD ONLY’))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid
1 row selected.
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL>
We can see that the view is based on SYS.TS$ and SYS.KCFISTSA which is a join between a Data Dictionary Base table and an X$ struct and we can also see the decode functions for various columns with all the values.
Another example is DBA_DATA_FILES:
SYS@tutor5d_nledu02 SQL> @sviewtext
SYS@tutor5d_nledu02 SQL> set long 20000
SYS@tutor5d_nledu02 SQL> col text for a100
SYS@tutor5d_nledu02 SQL> col view_name for a20
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL> select view_name,text from dba_views
2 where view_name like upper (‘%&&viewname%’);
Enter value for viewname: DBA_DATA_FILES
old 2: where view_name like upper (‘%&&viewname%’)
new 2: where view_name like upper (‘%DBA_DATA_FILES%’)
Hit Any Key to Continue
VIEW_NAME
——————–
TEXT
——————————————————————————-
DBA_DATA_FILES
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
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, ‘INVALID’, 2, ‘AVAILABLE’, ‘UNDEFINED’),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, ‘NO’, ‘YES’), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, ‘SYSOFF’, ‘SYSTEM’),
decode(bitand(fe.festa, 18), 0, ‘OFFLINE’, 2, ‘ONLINE’, ‘RECOVER’))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#
1 row selected.
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL>
Here we see that the view does a Union All Query to support multiple blocksizes but that each part of the query also refers to the base tables, X$ structs, and decode functions. Note also that in this view V$DBFILE is joined to FILE$ permitting the query to obtain the full path name of each data file is it appears in the controlfile. This explains why an “ALTER DATABASE RENAME FILE” command may be done with the database in MOUNT state when trhere is no access to the Data Dictionary. Only the CONTROL FILE is modified but once the database is opened DBA_DATA_FILES has the correct path names precisely because it is doing the join between FILE$ and V$DBFILE and thus getting the up to date path name from the control file.
A similar query may be done for Dynamic Performance views as follows:
set long 5000
col view_definition for a50
select view_name, view_definition
from v$fixed_view_definition
where view_name like upper(‘%&viewname%’);
undefine viewname
Lets now see what we get by looking at simple V$ view:
SYS@tutor5d_nledu02 SQL> /
Enter value for viewname: V$OPTION
old 3: where view_name like ‘%&viewname%’
new 3: where view_name like ‘%V$OPTION%’
Hit Any Key to Continue
VIEW_NAME VIEW_DEFINITION
——————– ————————————————–
GV$OPTION select inst_id,parameter, value from x$option
V$OPTION select PARAMETER , VALUE from GV$OPTION where inst_id = USERENV(‘Instance’)
2 rows selected.
SYS@tutor5d_nledu02 SQL>
First note how the V$ view queries the GV$ view of the same name for the current instance. This is generally true for all the V$ views. And note that we can see the X$ structs accessed by the GV$ View as well. Now lets try this with a slightly more complex V$ view:
SYS@tutor5d_nledu02 SQL> select view_name, view_definition
2 from v$fixed_view_definition
3 where view_name like upper(‘%&viewname%’);
Enter value for viewname: v$logfile
old 3: where view_name like upper(‘%&viewname%’)
new 3: where view_name like upper(‘%v$logfile%’)
Hit Any Key to Continue
VIEW_NAME VIEW_DEFINITION
—————————— ————————————————–
GV$LOGFILE select inst_id,fnfno, decode(fnflg,0,”, decode(bitand(fnflg,1),1,’INVALID’, decode(bitand(fnflg,2),2,’STALE’, decode(bitand(fnflg,4),4,’DELETED’, decode(bitand(fnflg,8+32),8,”,32,”,40,”,’UNKNOWN’))))), decode(bitand(fnflg,8),0,’ONLINE’,'STANDBY’), fnnam, decode(bitand(fnflg, 32),0,’NO’,'YES’) from x$kccfn where fnnam is not null and fntyp=3
V$LOGFILE select GROUP# , STATUS , TYPE , MEMBER, IS_RECOVERY_DEST_FILE from GV$LOGFILE where inst_id = USERENV(‘Instance’)
2 rows selected.
SYS@tutor5d_nledu02 SQL> undefine viewname
Once again the decodes are visible as well as the underlying structs.
In some cases one may also learn additional things by examining the queries in the views.
I find this a useful way to help people learn about important Oracle DBA views and the values one may use for filtering queries and I use this as a teaching tool for basic DBA courses at Oracle University. If you are somewhat new to Oracle DBA work, then have a go at using these and try some of the Data Dictionary or Dynamic Performance views that you commonly use.
