Finding particular Data Dictionary Table or View.

Hi,
Sometimes I need to execute some selects into database and to get it done I have to remember the name of some Data Dictionary Tables and/or Dynamic Performance views regularly, but sometimes I really don't remember that particular view. For sure it’s not easy to remember all the dictionary views!

Instead, there is a data dictionary view called “DICTIONARY” and this view can help us to find the particular or list of pertinent views of any object in the database that we are looking for.

From oracle documentation 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. Lets try it...

SQL> desc dictionary
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

SQL>

Please check this out for more info:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4196.htm#i1629189

The O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges.
If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior).
The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in the SYS schema.
Read more about here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams134.htm#CHDGCHHA

1-) In order to find the list of data dictionary tables and dynamic performance views.

SQL>SELECT * FROM dict;

2-) In order to find the particular data dictionary table or dynamic performance view.

SELECT * FROM dictionary WHERE table_name LIKE '%Search_String%';

Playing with this view...

SQL> SELECT * FROM dictionary WHERE table_name LIKE '%REDO%';

TABLE_NAME COMMENTS
------------------ --------------------------------------------------------
V$BACKUP_REDOLOG Synonym for V_$BACKUP_REDOLOG
GV$BACKUP_REDOLOG Synonym for GV_$BACKUP_REDOLOG

SQL> SELECT * FROM dictionary WHERE table_name='DICTIONARY';

TABLE_NAME COMMENTS
------------ --------------------------------------------------------
DICTIONARY Description of data dictionary tables and views

SQL>

Note: While querying, either the complete word DICTIONARY or first four characters DICT, which is a synonym for DICTIONARY, can be used.

Reference at "Oracle® Database Reference 10g Release 2 (10.2)"
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

Well, for me this is very helpfull!

Happy Reading!
Marcello Travassos.