Oracle Object Name Uniqueness
Have you ever wondered why it is possible to create an index with the same name as the table? Or why it is not possible to create a PL/SQL procedure with the same name?
SQL> CREATE TABLE difu(id number);
Table created.
SQL> CREATE INDEX difu ON difu(id);
Index created.
We have now two objects with the same name:
SQL> col object_name form a20
SQL> select object_name, object_type from dba_objects where object_name='DIFU';
OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
DIFU TABLE
DIFU INDEX
But trying to execute
CREATE OR REPLACE PROCEDURE difu
IS
BEGIN
dbms_output.put_line('Hello world!');
END;
/
results in ORA-00955: name is already used by an existing object! Why?
There is a column NAMESPACE in DBA_OBJECTS:
SQL> select distinct OBJECT_TYPE, NAMESPACE from DBA_OBJECTS order by NAMESPACE, OBJECT_TYPE;
OBJECT_TYPE NAMESPACE
----------------------- ----------
DESTINATION 1
FUNCTION 1
INDEXTYPE 1
JOB 1
JOB CLASS 1
LIBRARY 1
MLE LANGUAGE 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 1
PACKAGE BODY 2
TYPE BODY 2
TRIGGER 3
INDEX 4
INDEX PARTITION 4
CLUSTER 5
LOB 8
LOB PARTITION 8
DIRECTORY 9
QUEUE 10
CONTEXT 21
RULE SET 23
CONSUMER GROUP 24
RESOURCE PLAN 24
XML SCHEMA 25
RULE 36
EVALUATION CONTEXT 38
UNDEFINED 51
UNDEFINED 52
EDITION 64
CONTAINER 88
UNIFIED AUDIT POLICY 93
LOCKDOWN PROFILE 132
MLE LANGUAGE 147
45 rows selected.
As you can see a TABLE and a PROCEDURE are in the same namespace, an INDEX is in another namespace. Only objects in different namespaces may have the same names. If I was asked, I recommend not to use identical names anyway. There may be exceptions, but I think generally there is no need.