How to find constraints and indexes on a table in Oracle?

Posted: September 01, 2010

Sometimes you have to work with an old version of an Oracle database. And if you are unlucky, SQL Developer cannot connect with that database. You can use less sophisticated tools to connect to the database. How do you find the constraints on a table?

To find the constraints on a table use the following query:

select * from DBA_CONSTRAINTS where table_name='<table name>';

Columns of a primary key can be retrieved as follows:

select * from DBA_CONS_COLUMNS where constraint_name='<constraint name>';

The indexes of a table can be retrieved with:

select * from DBA_INDEXES where table_name='<table name>';

Finally, the columns of an index are retrieved with:

select * from DBA_IND_COLUMNS where table_name='<table name>';