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

From: Ben Ryan <benryan_at_my-deja.com>
Date: Sat, 19 Feb 2000 23:49:09 GMT
Message-ID: <88na5k$dsj$1@nnrp1.deja.com>

After reading your e-mail (Thomas Kyte - see below) and altering my original SELECT statement gives.

SELECT c.constraint_name,s.con#,s2.enabled,RPAD(o.object_name,20) object_name
  FROM sys.con$ s,user_constraints c, sys.cdef$ s2, user_objects o

 WHERE c.constraint_type IN ('P','U')
   AND c.table_name = replace_with_name_of_table
   AND c.constraint_name = s.name
   AND s.con# = s2.con#

   AND s2.enabled = o.object_id;

Which, as far as I can tell, works on both 7.3.4.4 and 8.0.5.

Given my understanding that Oracle Corp. reserves the right to change tables sys.cdef$ and sys.con$ without notice, I have tried to stay away from writing anything that depends upon them.

My reading of your e-mail is, therefore, there is no approved/supported way of doing this. Is that correct?

Thanks, Ben

In a private communication Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>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....
>
>--
>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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 19 2000 - 17:49:09 CST