How to find the particular Data Dictionary Table or Dynamic Performance View (V$)

How to find the particular Data Dictionary Table or Dynamic Performance View (V$)

Even though we use some of the important Data Dictionary Tables and Dynamic 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.

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.