Posted by It all seems simple enough. You pick a name for an object…and that is the only object that have than name right ? For example:
SQL> create table MY_UNIQUE_NAME ( x int );
Table created.
SQL>
SQL> create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME;
create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME
*
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object
SQL>
SQL> create view MY_UNIQUE_NAME as select * from MY_UNIQUE_NAME;
create view MY_UNIQUE_NAME as select * from MY_UNIQUE_NAME
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL> create package MY_UNIQUE_NAME is
2 x int;
3 end;
4 /
create package MY_UNIQUE_NAME is
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
So naturally we’d expect an error if we continued on with other object types. But then this happens ?
SQL> create or replace
2 trigger MY_UNIQUE_NAME
3 before insert on MY_UNIQUE_NAME
4 begin
5 null;
6 end;
7 /
Trigger created.
SQL>
SQL> create index MY_UNIQUE_NAME on MY_UNIQUE_NAME ( x );
Index created.
It might all seem a bit random. But this is all related to what is known as the NAMESPACE, that is, names of certain objects must be unique within a particular NAMESPACE. We can see the namespaces for the objects by querying DBA_OBJECTS
SQL> select distinct namespace, object_type from dba_Objects order by 1,2;
NAMESPACE OBJECT_TYPE
---------- -----------------------
1 CREDENTIAL
1 DESTINATION
1 FUNCTION
1 INDEXTYPE
1 JAVA CLASS
1 JOB
1 JOB CLASS
1 LIBRARY
1 OPERATOR
1 PACKAGE
1 PROCEDURE
1 PROGRAM
1 SCHEDULE
1 SCHEDULER GROUP
1 SEQUENCE
1 SYNONYM
1 TABLE
1 TABLE PARTITION
1 TABLE SUBPARTITION
1 TYPE
1 VIEW
1 WINDOW
2 PACKAGE BODY
2 TYPE BODY
3 TRIGGER
4 INDEX
4 INDEX PARTITION
5 CLUSTER
8 LOB
8 LOB PARTITION
9 DIRECTORY
10 QUEUE
13 JAVA SOURCE
14 JAVA RESOURCE
19 MATERIALIZED VIEW
21 CONTEXT
23 RULE SET
24 CONSUMER GROUP
24 RESOURCE PLAN
25 XML SCHEMA
32 JAVA DATA
36 RULE
38 EVALUATION CONTEXT
51 UNDEFINED
52 UNDEFINED
64 EDITION
88 CONTAINER
93 UNIFIED AUDIT POLICY
132 LOCKDOWN PROFILE
DATABASE LINK
50 rows selected.
Because indexes, triggers are in a different namespace to tables, synonyms, procedures, packages etc, they can share the same name.
I’d still probably recommend unique names across all namespaces, just so there is no confusion.
Learning is not a spectator sport 