Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Joining constraints to indexes in the system dictionary (Oracle8)

Re: Joining constraints to indexes in the system dictionary (Oracle8)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 19 Feb 2000 17:15:41 -0500
Message-ID: <qu4uas01kbiqofthrb5orau32o864iqs6r@4ax.com>


A copy of this was sent to Ben Ryan <benryan_at_my-deja.com> (if that email address didn't require changing) On Sat, 19 Feb 2000 20:03:18 GMT, you wrote:

>Oracle 8.0.5
>
>Question:
>In Oracle8 what is the approved way to join user_constraints
>to user_indexes for constraints which are primary key or unique key
>constraints?
>
>Background:
>I have seen scripts for Oracle7 where it relies on the fact that the
>name of the index matches the name of the constraint. e.g.
>
>SELECT ... FROM user_constraints c, user_indexes i
>WHERE c.constraint_type IN ('P','U')
>and c.constraint_name = i.index_name;
>
>However, I understand that in Oracle8 primary key constraints can be
>enforced via pre-existing indexes. Meaning, I assume, the names will not
>necessarily match.
>
>Thanks, Ben
>

well -- its always been that way actually, 8.0 didn't change anything, the index name never had to be the same as the constraint:

tkyte_at_ORA734.WORLD> create table t ( x int ); Table created.

tkyte_at_ORA734.WORLD> create unique index foo on t(x); Index created.

tkyte_at_ORA734.WORLD> alter table t add constraint t_pk primary key(x); Table altered.

tkyte_at_ORA734.WORLD> select object_type, object_name,

  2                  decode(status,'INVALID','*','') status,
  3                  tablespace_name

  4 from user_objects a, user_segments b   5 where a.object_name = b.segment_name (+)   6 order by object_type, object_name
  7 /
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        FOO                              SYSTEM

TABLE        T                                SYSTEM


The index is FOO, the constraint however it T_PK. This is in 7.3.

The data you need is hidden in the SYS schema, in the CDEF$ and CON$ tables. You can get the object_id of the index used to enforce this constraint via the query:

tkyte_at_ORA734.WORLD> select enabled
  2 from sys.cdef$
  3 where con# = ( select con# from sys.con$

  4                   where owner# = uid and name = 'T_PK' );

   ENABLED


      1067

and then to get the index:

tkyte_at_ORA734.WORLD> select object_name, object_type   2 from user_objects where object_id =   3 (
  4 select enabled
  5 from sys.cdef$
  6 where con# = ( select con# from sys.con$

  7                   where owner# = uid and name = 'T_PK' )
  8 )
  9 /
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------
FOO                            INDEX


You'll need create your own Views in the SYS schema to expose this data or grant select on cdef$, con$ to yourself....

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Feb 19 2000 - 16:15:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US