Even though we use some of the important Data Dictionary Tables andDynamic Performance (V$) views regularly, sometimes we fall short to recall a particular view, i.e. It’s not easy to remember all the dictionary views.
And also, many times, it’s been asked by novice in the OTN Forums that
What is the view to find the information about data files, tablespaces, etc.,?
What are the list of Data Dictionary and Dynamic Performance Views in so and so Oracle Version?
Well, I hope all of we know that there is a data dictionary view called “DICTIONARY”, using this DICTIONARY view, we can find the particular or list of pertinent views of any object in the database.
What is the DICTIONARY view?
The DICTIONARY view is a data dictionary view used to find the list of Data Dictionary and Dynamic Performance (V$) views, and it contains description of data dictionary tables and views. It has two columns, one is ‘TABLE_NAME’ – Name of the object and another column is ‘COMMENTS’ – Text comment on the object.
Discription:
SQL> desc DICTIONARY;
Name Null? Type
--------------------- -------- --------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
Usage:
To find the list of data dictionary tables and dynamic performance views.
SELECT * FROM dict;
To find the particular data dictionary table or dynamic performance view.
SELECT * FROM dictionary
WHERE table_name LIKE ‘%Search String%’;
i.e.
SELECT * FROM dictionary
WHERE table_name LIKE ‘%TABLESP%’;
Example:
SQL> SELECT * FROM dictionary
WHERE table_name='DICTIONARY';
TABLE_NAME
------------------------------
COMMENTS
-----------------------------------------------
DICTIONARY
Description of data dictionary tables and views
Note: While querying, either the complete word DICTIONARY or first four characters DICT, which is a synonym for DICTIONARY, can be used.
Reference: Oracle® Database Reference 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm
