Duh, thx Joel Patterson Database Administrator 904 727-2546 From: Phillip Jones [mailto:phil@xxxxxxxxxx] Sent: Thursday, November 10, 2011 8:55 AM To: Patterson, Joel Cc: ORACLE-L Subject: Re: naming conventions (constraints and indexes) Is the object owner really 'FDH' ? Looks like it's 'DBMON' if your sqlplus prompt is anything to go by. Cheers, Phil On Thu, Nov 10, 2011 at 1:46 PM, <Joel.Patterson@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx>> wrote: The table creation was actually taken from an example where Tom Kyte responded to a question as to the best way/order to create the index/constratint, 28 Apr 2008. http://asktom.oracle.com/pls/asktom/f?p0:11:1892071625408060::::P11_QUESTION_ID:36858373078604 However, as part of this process I will review a best practices method of creating these things as has been pointed out in a couple places. Personally, I have been kind of living with the index drop issue for a long long time, so it wasn't a major concern. I think I'm going to return and review the issue again while I'm on the subject of naming and standards. I cannot seem to get any rows returned from your select from dba_objects query 10.2.0.4 or 11.2.0.1. DBMON @ lawrtdev> CREATE TABLE EXAMPLE_TABLE ( 2 PRIM_COL number constraint EXAMPLE_TABLE_PK primary key 3 using index ( CREATE INDEX EXAMPLE_TABLE_PX ON 4 EXAMPLE_TABLE(PRIM_COL)), 5 UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE 6 using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on 7 EXAMPLE_TABLE(UNIQ_COL)), 8 junk varchar2(10) 9 ); Table created. DBMON @ lawrtdev> DBMON @ lawrtdev> create table t (veld1 number(10)); Table created. DBMON @ lawrtdev> insert into t values (1); 1 row created. DBMON @ lawrtdev> insert into example_table values (1,1,'Example'); 1 row created. DBMON @ lawrtdev> commit; Commit complete. DBMON @ lawrtdev> alter table t add constraint t_pk primary key (veld1) 2 using index (create unique index t_i on t (veld1)); Table altered. DBMON @ lawrtdev> DBMON @ lawrtdev> select index_name, index_type, uniqueness, generated 2 from user_indexes where table_name in ('T', 'EXAMPLE_TABLE'); INDEX_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------------------- --------- - EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N EXAMPLE_TABLE_PX NORMAL NONUNIQUE N T_I NORMAL UNIQUE N 3 rows selected. DBMON @ lawrtdev> DBMON @ lawrtdev> select constraint_name, constraint_type, index_name, generated 2 from user_constraints where table_name in ('T', 'EXAMPLE_TABLE'); CONSTRAINT_NAME C INDEX_NAME GENERATED ------------------------------ - ------------------------------ -------------- EXAMPLE_TABLE_PK P EXAMPLE_TABLE_PX USER NAME EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX USER NAME T_PK P T_I USER NAME 3 rows selected. DBMON @ lawrtdev> DBMON @ lawrtdev> DBMON @ lawrtdev> select 2 obj_t.owner table_owner, obj_t.object_name table_name, 3 obj_i.owner index_owner, obj_i.object_name index_name, 4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit, 5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index 6 from 7 dba_objects obj_t, 8 dba_objects obj_i, 9 sys.ind$ ind 10 where 11 ind.bo#<http://ind.bo> = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'FDH' 14 and obj_i.object_name = 'T_I' 15 -- ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX') 16 order by table_name, index_name ; no rows selected DBMON @ lawrtdev> DBMON @ lawrtdev> alter table t drop constraint t_pk; Table altered. DBMON @ lawrtdev> DBMON @ lawrtdev> select index_name from user_indexes where table_name in ('T', 'EXAMPLE_TABLE'); INDEX_NAME ------------------------------ EXAMPLE_TABLE_UNIQ_COL_UX EXAMPLE_TABLE_PX 2 rows selected. DBMON @ lawrtdev> DBMON @ lawrtdev> alter table t add primary key (veld1) 2 using index (create unique index t_i on t (veld1)); Table altered. DBMON @ lawrtdev> DBMON @ lawrtdev> select index_name, index_type, uniqueness, generated 2 from user_indexes where table_name in ('T', 'EXAMPLE_TABLE'); INDEX_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------------------- --------- - EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N EXAMPLE_TABLE_PX NORMAL NONUNIQUE N T_I NORMAL UNIQUE Y 3 rows selected. DBMON @ lawrtdev> DBMON @ lawrtdev> select 2 obj_t.owner table_owner, obj_t.object_name table_name, 3 obj_i.owner index_owner, obj_i.object_name index_name, 4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit, 5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index 6 from 7 dba_objects obj_t, 8 dba_objects obj_i, 9 sys.ind$ ind 10 where 11 ind.bo#<http://ind.bo> = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'FDH' 14 and obj_i.object_name in 15 ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX') 16 order by table_name, index_name; no rows selected DBMON @ lawrtdev> DBMON @ lawrtdev> set echo off Joel Patterson Database Administrator 904 727-2546<tel:904%20727-2546> -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l