Data Dictionary and Dynamic performance view

The Database Library is built on a Data Dictionary, which provides a complete description of record layouts and indexes of the database, for validation and efficient data access. You can use the data dictionary for automated database creation, including building tables, indexes, and referential constraints, and granting access rights to individual users and groups. The database dictionary supports the concept of Attached Objects, which allow database records to include compressed BLOBs (Binary Large Objects) containing images, text, sounds, video, documents, spreadsheets, or programmer-defined data types.

You can request information about the dictionary table

SELECT comments
FROM dictionary WHERE table_name='DICTIONARY'
COMMENTS
--------------------------------------------------------------------------------
Description of data dictionary tables and views

DBA_VIEWS

Description of dba_views

·   OWNER               Owner of the view
·   VIEW_NAME           Name of the view
·   TEXT_LENGTH         Length of the view text
·   TEXT                View text
·   TYPE_TEXT_LENGTH    Length of the type clause of the object view
·   TYPE_TEXT           Type clause of the object view
·   OID_TEXT_LENGTH     Length of the WITH OBJECT OID clause of the object view
·   OID_TEXT            WITH OBJECT OID clause of the object view
·   VIEW_TYPE_OWNER     Owner of the type of the view if the view is an object view
·   VIEW_TYPE           Type of the view if the view is an object view
·   SUPERVIEW_NAME      Name of the superif view is a subview

To get Full list of your Data Dictionary view run following query on your database.

SELECT *
from  dba_views
where owner = 'SYS'
      and view_name like 'DBA%';
ORDER BY view_name desc

[N.B: You can use TEXT field to view he information about the objects]

ALL_VIEWS

Description of all_views

·   OWNER               Owner of the view
·   VIEW_NAME           Name of the view
·   TEXT_LENGTH         Length of the view text
·   TEXT                View text
·   TYPE_TEXT_LENGTH    Length of the type clause of the object view
·   TYPE_TEXT           Type clause of the object view
·   OID_TEXT_LENGTH     Length of the WITH OBJECT OID clause of the object view
·   OID_TEXT            WITH OBJECT OID clause of the object view
·   VIEW_TYPE_OWNER     Owner of the type of the view if the view is an object view
·   VIEW_TYPE           Type of the view if the view is an object view
·   SUPERVIEW_NAME      Name of the superif view is a subview

You can also get Full list of your Data Dictionary view run following query.

select TEXT
from ALL_VIEWS
where owner='SYSTEM'

V$FIXED_TABLE

This Dynamic performance view Contains the name of all V$, X$ and GV$ tables.



Description of V$FIXED_TABLE

·   NAME          Name of the object
·   OBJECT_ID     Identifier of the fixed object
·   TYPE          Object type (TABLE | VIEW)
·   TABLE_NUM     Number that identifies the dynamic performance table if it is of type TABLE

To get Full list of Dynamic Performance View (V$) run following query

SELECT name
from   V$FIXED_TABLE
where  name like 'V$%';

When using these views you should have timed_statistics set to true in your init.ora file. This has minimal effect on performance and can be permanently left on (TIMED_STATISTICS=TRUE)

GV$ views are dynamic performance views in a parallel server environment. There is a GV$ view for almost every V$ view. These have an additional column INST_ID (instance number)