Sort of. We do not directly keep track of this but we can deduce it (almost).

If the index name of the index supporting a primary/unique constraint is system generated -- then we can tell for sure.

If the index name of the index supporting it is the SAME as the constraint name -- we are pretty sure it was generated. PRETTY sure (i can create an index I1 on the pimary key columns and then create a primary key I1 -- we did not generate the index in this case).

If the index name if the index is not the same -- then we know it is not generated.

here is a view that works well (create as SYS)


sys@TKYTE816> create or replace view pk_indexes
2 as
3 select t.name table_name
4 , u.name owner
5 , c.name constraint_name
6 , i.name index_name
7 , decode(bitand(i.flags, 4), 4, 'Yes',
decode( i.name, c.name, 'Probably', 'No') )
8 generated
9 from sys.cdef$ cd
10 , sys.con$ c
11 , sys.obj$ t
12 , sys.obj$ i
13 , sys.user$ u
14 where cd.type# between 2 and 3
15 and cd.con# = c.con#
16 and cd.obj# = t.obj#
17 and cd.enabled = i.obj#
18 and c.owner# = u.user#
19 and c.owner# = uid
20 /

View created.

sys@TKYTE816> create public synonym pk_indexes for pk_indexes;
sys@TKYTE816> grant select on pk_indexes to public;


And to test it we can:


tkyte@TKYTE816> create table t ( x int primary key,
2 y int,
3 z int,
4 constraint y_unique unique(y) );
Table created.

tkyte@TKYTE816> create index z_idx on t(z);
Index created.

tkyte@TKYTE816> alter table t add constraint z_unique unique(z);
Table altered.

tkyte@TKYTE816> select * from pk_indexes
2 /

TABLE_NAME OWNER CONSTRAINT_ INDEX_NAME GENERATE
---------- ----- ----------- ----------- --------
T TKYTE SYS_C001260 SYS_C001260 Yes
T TKYTE Y_UNIQUE Y_UNIQUE Probably
T TKYTE Z_UNIQUE Z_IDX No