Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Joining constraints to indexes in the system dictionary (Oracle8)
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
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 )
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