Object Namespaces in Oracle

With an example I will start the topic,

SQL> create table name (a number);
Table created.

SQL> create index name on name(a);
Index created.

SQL> desc name;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> create view name as select * from name;
create view name as select * from name
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here, we see that I can create an index with the same name as of table but I can't create a view with the same name as of a table.

Now it is necessary to know which type of objects can have the same name and which are not. For this it is necessary to introduce the concept of a namespace. A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name.

These object types all share the same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types
Thus it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

These object types each have their own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

Non schema objects has their own namespace

• User roles
• Public synonyms
• Public database links
• Tablespaces
• Profiles
• Parameter files (PFILEs) and server parameter files (SPFILEs)

So two non schema objects can have same name. Like the word users can be both tablespace name and role name.