Choose your names wisely

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.

connor_speaking

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.