How do I find information about a database object (table, index, constraint etc.)

The Oracle (tm) Users' Co-Operative FAQ

Repeat of the short-form of the question on the index page.

You look in the dictionary. Information on the structure and storage characteristics for just about any Oracle object can be found in the rdbms data dictionary. To find a list of all the dictionary tables available to you, you can query the view dictionary which also includes the dynamic performance views, v$, tables:

	Column table_name format a30
	Column comments   format a40
	select * from dictionary;
	TABLE_NAME                     COMMENTS
	------------------------------ ----------------------------------------
	DBA_2PC_NEIGHBORS              information about incoming and outgoing
	                               connections for pending transactions
	DBA_2PC_PENDING                info about distributed transactions awai
	                               ting recovery
	DBA_ALL_TABLES                 Description of all object and relational
	                               tables in the database

There are generally three versions of every dictionary table, which are actually views, of the form: all_tables, dba_tables, and user_tables.

	USER_x	provides information on all 'x' that you own
	All_x	provides information on all 'x' that you have privilege on
	DBA_x	provides information on all 'x' that exist in database

And x is often an Oracle object type such as tables, indexes, constraints, views, synonyms, and so on. Normally the only difference between the columns shown by each view is that the owner column is added to the all and dba versions since the user version would not need the owner column.

Some of the more commonly accessed dictionary views are:

	all_tables		all the tables the current user can access
	all_tab_columns	all the columns by table for the tables the current user can access
	all_constraints	all constraints (PK, FK, Unique, check, and Not Null) on tables accessible by current user
	all_indexes		all the indexes on the tables the current user can access
	all_ind_columns	all the columns in the indexes on the tables the current user can access
	all_objects		all objects the current user has access to
	all_source		all source for stored procedures, functions, and packages the current user has execute on
	all_synonyms		all synonyms accessible to the current user
	all_triggers		all triggers owned by or on tables owned by the current user, not on all accessible tables
	all_users		all the users visible to the current user
	all_views		all views accessible by the current user (includes source)

There are more than 300 dictionary views available to you. There are views for privileges granted, auditing, histograms, partitioned objects, and nearly everything else in Oracle. It takes time and you need to learn about them all.

Further reading: For version 7.3 up see the Oracle {version#} Reference manual for a listing and descriptions of the Dictionary views and the dynamic performance tables.