The Data Dictionary

Information about a database is maintained by Oracle in a set of tables called the data dictionary. These tables contain information about objects, users and storage structures. The tables are created by Oracle when the database is created and are automatically maintained by Oracle.

Users access these tables through a set of views. The views fall into three categories:

Views with the prefix USER: contain information on objects owned by the user
Views with the prefix ALL: contain information on objects the user can access
Views with the prefix DBA: contain information on the whole database

Views with prefix V$:  dynamic performance views(memory, locking, …)

The data dictionary tables are normal SQL tables with public read access; therefore the data in these tables can be obtained by any user using the SELECT command. To obtain a list of the columns in a data dictionary table, use the DESCRIBE command.

Some of the user data dictionary views are described in the following table:

View Name	Description
DICTIONARY (Synonym DICT)	Description of all data dictionary tables and views
USER_CATALOG (Synonym CAT)	List of all tables, views, synonyms and sequences owned by the user
USER_CONS_COLUMNS	Columns associated with constraint names
USER_CONSTRAINTS	Constraints on user's tables
USER_IND_COLUMNS	Columns associated with indexes
USER_INDEXES (synonym IND)	Description of indexes owned by the user
USER_OBJECTS (Synonym OBJ)	Description of all object's owned by the user
USER_SEQUENCES (Synonym SEQ)	Description of the user's sequences
USER_TABLES (Synonym TABS)	Description of user's tables
USER_TAB_COLUMNS(Synonym COLS)	Description of columns in all tables and views owned by the user
USER_TRIGGERS	Description of the user's triggers
USER_USERS	Contains information on the user
USER_VIEWS	Text of views owned by the user

Examples:

The following retrieval will display the table names of all tables owned by the user:

select table_name from tabs;

The following retrieval will list the table name of all tables in the data dictionary prefixed by USER:

select table_name from dict
        where table_name like 'USER%';

In addition to storing information on the user tables in the database, the data dictionary tables contain information on the data dictionary tables as well. The following retrieval will display the same information as the DESCRIBE command for the data dictionary table: USER_OBJECTS:

select table_name, data_type, nullable
        from all_tab_columns
        where table_name = 'USER_OBJECTS';

Note that the ALL prefix must be used in the table name in the FROM clause since the user does not own the data dictionary tables.