The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, santhoshreddy.

Asked: November 07, 2016 - 6:06 am UTC

Last updated: February 15, 2021 - 4:01 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

What is Namespace in oracle and what is the difference between Namespace and Namespace?
I found the namespace word in below document under data base object naming rule (8 point)

https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements008.htm#SQLRF51129

and Connor said...

A schema is the entity that provides "ownership" to objects.

A namespace is the categorisation which objects are identified (and hence typically will need unique naming).

eg

<code>
SQL> select object_type, namespace
2 from dba_objects
3 group by object_type, namespace
4 order by 2,1
5 /

OBJECT_TYPE NAMESPACE
----------------------- ----------
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 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
...
<code>

So can you see that a table and a view cannot have the same name (they are both in namespace 1). But a directory and a table could have the same name, because they are in different namespaces.

Rating

(5 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

santhoshreddy podduturi, November 08, 2016 - 6:37 am UTC

santhoshreddy podduturi, November 08, 2016 - 6:38 am UTC

Would you Explane briefly because i did not understand.

November 09, 2016 - 2:00 am UTC

SQL> create table blah ( x int );

Table created.

SQL> create index blah on blah ( x ) ;

Index created.

SQL> create procedure blah is begin null ; end ;
  2  /
create procedure blah is begin null ; end ;
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

A table and an index can have the same name because they are in a different namespace. A table and a procedure *cannot* have the same name, because they are in the same namespace.

Thanks for the explanation.

Praveen, November 08, 2016 - 12:55 pm UTC

santhoshreddy podduturi, November 10, 2016 - 7:22 am UTC

So,Schema is a logical structure and NAME SPACE is Also a logic inside Schema to avoid the duplicate names of objects belongs to same namespace.
Am i Correct?

November 11, 2016 - 8:23 am UTC

Yes

What about table partitions?

Paulo Terça, February 12, 2021 - 10:43 am UTC

Thank you for the explanation. But why then can I create two different tables with the same partition name?

SQL> create table t1( a number, b date)
  2  partition by range (b)
  3  (
  4    partition part1 values less than (date '2020-01-01')
  5  );

Table created.

SQL> create table t2( a number, b date)
  2  partition by range (b)
  3  (
  4    partition part1 values less than (date '2020-01-01')
  5  );

Table created.

Or, for that matter, create a table with a partition with the name of the table, since table and table partition are in the same namespace?

SQL> create table t3( a number, b date)
  2  partition by range (b)
  3  (
  4    partition t3 values less than (date '2020-01-01')
  5  );

Table created.

Thanks.

February 15, 2021 - 4:01 pm UTC

Partitions are subobjects of tables/indexes. So the fully qualified names here are:

t1.part1
t2.part1
t3.t3

These are all unique => no clash!

For example:

create table t1( a number, b date)
partition by range (b)
(
  partition part1 values less than (date '2020-01-01')
);

create table t2( a number, b date)
partition by range (b)
(
  partition part1 values less than (date '2020-01-01')
);

create table t3( a number, b date)
partition by range (b) (
 partition t3 values less than (date '2020-01-01'), 
 partition other values less than (date '2021-01-01')
);
 
select object_name, subobject_name, object_type 
from   user_objects
where  created > sysdate - 1/24;

OBJECT_NAME    SUBOBJECT_NAME    OBJECT_TYPE       
T1             PART1             TABLE PARTITION    
T1             <null>            TABLE              
T2             PART1             TABLE PARTITION    
T2             <null>            TABLE              
T3             OTHER             TABLE PARTITION    
T3             T3                TABLE PARTITION    
T3             <null>            TABLE

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for the explanation.

What about table partitions?

More to Explore

PL/SQL demos

PL/SQL docs

Difference between schema and namespace

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for the explanation.

What about table partitions?

More to Explore

PL/SQL demos

PL/SQL docs