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.