Consider the situation below,

create table baseTab( key integer primary key, val varchar2(10));

insert into baseTab values( 1, 'a' );
insert into baseTab values( 2, 'b' );
insert into baseTab values( 3, 'c' );
insert into baseTab values( 4, '');

Now creating the Materialized View

create materialized view MyMV
  REFRESH COMPLETE 
  as select * from BaseTab
;

Now, when I am creating this 'MyMV' view, it is internally creating a storage for the data of the SELECT query. The storage is created as a table with the name of 'MyMV'. We can see this object in sys.obj$ e.g.

SQL> select obj#, Name, type#  from sys.obj$ where name like 'MYMV';      

      OBJ# NAME           TYPE#
---------- ------------------------------ ----------
     34262 MYMV        2
     34264 MYMV       42

The result is showing two records, of course one is for MATERIALIED VIEW & other is TABLE. My question is, how oracle is allowing the same object name for two objects ? As per my understanding, Tables & Materialized Views are stored in the same namespace, hence it should now allow to create the same named object in the same namespace.
- Is their any exception for Materialized View ?
- In case of MVs, the Table object is created internally & hence it is allowed ?

Can you share your views on this please ?


Thanks,
-Piyush

ops$tkyte%ORA11GR2> create table t1 as select * from dual;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from dual;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create materialized view t1 as select * from dual;
create materialized view t1 as select * from dual
                                             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte%ORA11GR2> create materialized view t2 on prebuilt table as select * from dual;

Materialized view created.