Re: Why Oracle Enforce Name Uniquness ???!!!

On Sat, 04 Nov 2006 17:16:34 -0500, Serge Rielau wrote:

> It certainly is an interesting question why Oracle lumps routines and 
> packages into the same name space with tables.
> Interestingly I was kind of expecting to see SCHEMA and PACKAGE to share 
> a name space. That would be my choice since both group objects together. :-)

Might this not be a difference in interpretation of 'schema' and what is included in a 'schema'?

According to Oracle's Concepts manual, in the chapter detailing 'Schema' we read:

"Schema objects can be created and manipulated with SQL and include the  following types of objects:

• Clusters
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Indexes and index types
• Java classes, Java resources, and Java sources
• Materialized views and materialized view logs
• Object tables, object types, and object views
• Operators
• Sequences
• Stored functions, procedures, and packages
• Synonyms
• Tables and index-organized tables
• Views "

At least that's what Oracle uses in 10gR2.

As far as object naming ... the SQL Reference manual provides the rules, in the 'Schema Object Names and Qualifiers' section of CHapter 2

"Within a namespace, no two objects can have the same name

 The following schema objects share one namespace:

• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types

 Each of the following schema objects has its own namespace:

• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions

 Because tables and views are in the same namespace, a table and a view in  the same schema cannot have the same name. However, tables and indexes  are in different namespaces. Therefore, a table and an index in the same  schema can have the same name.

 Each schema in the database has its own namespaces for the objects it  contains. This means, for example, that two tables in different schemas  are in different namespaces and can have the same name.

 Each of the following nonschema objects also has its own namespace:

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

 Because the objects in these namespaces are not contained in schemas,  these namespaces span the entire database. "

As to 'why' ... as I'd already mentioned, it's Oracle's sandbox so they can use the rules they wish. I'm sure there were historical reasons that have influenced recent decisions.

What I find interesting is that the original poster was surprised to get caught on this AND did not use the documentation.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.