Oracle:Data Dictionary

Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Data Dictionary

Data Dictionary

The data dictionary describes tables, columns, indexes and other objects in the database. The dictionary is held in the form of tables that can be read like any other tables. You cannot change any data in these tables manually (i.e. Using SQL INSERT, DELETE and UPDATE statements). Oracle implicitly changes these tables based upon your actions, such as:

• Creating, deleting or updating a table
• Creating, deleting or updating a view
• Creating, deleting or updating a index
• Creating, deleting or updating a trigger
• Creating, deleting or updating a procedure, function or package

The dictionary table DICTIONARY describes the tables that make up the data dictionary.

To display the names and descriptions of dictionary tables:

SELECT * FROM DICTIONARY;

Useful dictionary tables

TABLE NAME	DESCRIPTION
USER_CONSTRAINTS	Describes information regarding the constraint on tables defined by the user.
USER_CONS_COLUMNS	Describes information about the columns involved in constraints defined by the user.
USER_ERRORS	Describes the errors for all triggers and procedures that the application developer has attempted to register to the database.
USER_INDEXES	Lists the indexes that you own and those that other users have defined for tables that you own.
USER_IND_COLUMNS	Defines the attributes involved in an index, especially useful for composite indexes.
USER_SEQUENCES	Descriptions of all the users sequences.
USER_SOURCE	Describes all the Procedures, Functions and Packages defined by the user, including the source code.
USER_TABLES	Describes all the tables that you own.
USER_TAB_COLUMNS	Describes the names and column definitions of tables that you own.
USER_TRIGGERS	Defines all the triggers defined by the user, including the source code.
USER_TRIGGER_COLS	Defines the columns involved for a user defined trigger.
USER_VIEWS	Lists the subqueries that define the views you own.

For example:

SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES
ORDER BY TABLE_NAME

This lists what indexes you have on each table.

The dictionary tables can contain a lot of information, use DESCRIBE tablename to see what columns each dictionary table has before querying them.

Next Step

This completes the Miscellaneous Features section. Now return to the Workbook.