Re: Index Pre Check

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 05 Mar 2005 10:19:47 +0100
Message-ID: <g0ui21lenlrt1t4fq6s4h9187nma1gcu0e@4ax.com>

On 5 Mar 2005 00:56:09 -0800, "Shasi" <v.sasikumar_at_gmail.com> wrote:

>I am aware of the two data dictionaries but still i cannot exacly
>visualise because if the index already existing is along with a
>constraint say unique constraint then i need to drop the constraint .
>In that case i cannot i drop the index directly...can u please reply in
>detail regading this as it will be very useful to me

Ok,
what is in the dictionary can be viewed by looking up select * from dict where table_name /* in this case* / like '%CONSTRAINT%' This will result in
dba_constraints
user_constraints

as you can expect, the column definition is in dba_cons_columns and user_cons_columns respectively.

The dba_constraints *view* has a column constraint_type. In the docs you can read the allowed values are

'C'  for check
'P' for primary
'R' for referential (in this case the r_constraint column is not null,

pointing recursively to a second record in the same view) and 'U' for unique.

That's all there is to it.
Please note apart from the dict view, there is also a dict_columns_view, and two comments views on the dictionary. It is all there and it is all documented, and regrettably most people prefer not to read the documentation and wait for someone here to respond to their queries.
Mind you, I found this out ages ago, by doing some detective work myself. And that time I didn't have access to Usenet at all, and it was beyond me to start nagging Support for everything.

--
Sybrand Bakker, Senior Oracle DBA

Received on Sat Mar 05 2005 - 03:19:47 CST