Namespaces

Can we able to create an index with the same name as the table name ?

Yes . Because the table and index resides in the different namespaces.

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

So  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.

SQL> select namespace from v$librarycache;

NAMESPACE
———————————————
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA

11 rows selected.

Here there is an small test:

SQL> create table t1 as select *  from user_objects;

Table created.

SQL> create index t1 on t1(object_id);

Index created.

SQL>  CREATE OR REPLACE package t1 as (ENO VARCHAR2);
2  /
CREATE OR REPLACE package t1 as (ENO VARCHAR2);
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here we are getting the error because the packages and tables resides in the same namespace.

Advertisements

One thought on “Namespaces

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s