What you are seeing is somewhat misleading. You cannot create a table and a materialized view of the same name. However, when you create a materialized view, Oracle automatically creates a table by the same name in the ..._objects data dictionary views. When you select from that, you are selecting from the materialized view. So, there are not two different things to select from. Please see the demonstration below.
-- If you do not have an object named test_tab:
SCOTT@orcl_11gR2> column object_name format a30
SCOTT@orcl_11gR2> select object_type, status, object_name
2 from user_objects
3 where object_name = 'TEST_TAB'
4 /
no rows selected
-- and you created a tabled named test_tab:
SCOTT@orcl_11gR2> create table test_tab as select * from dept
2 /
Table created.
SCOTT@orcl_11gR2> select object_type, status, object_name
2 from user_objects
3 where object_name = 'TEST_TAB'
4 /
OBJECT_TYPE STATUS OBJECT_NAME
------------------- ------- ------------------------------
TABLE VALID TEST_TAB
1 row selected.
-- then you cannot created a materialized view named test_tab:
SCOTT@orcl_11gR2> create materialized view test_tab as
2 select * from dept
3 /
select * from dept
*
ERROR at line 2:
ORA-00955: name is already used by an existing object
SCOTT@orcl_11gR2> drop materialized view test_tab
2 /
drop materialized view test_tab
*
ERROR at line 1:
ORA-12003: materialized view "SCOTT"."TEST_TAB" does not exist
-- if you do not have an object names test_tab:
SCOTT@orcl_11gR2> drop table test_tab
2 /
Table dropped.
SCOTT@orcl_11gR2> select object_type, status, object_name
2 from user_objects
3 where object_name = 'TEST_TAB'
4 /
no rows selected
-- and you created a materialized view names test_tab:
SCOTT@orcl_11gR2> create materialized view test_tab
2 as select * from dept
3 /
Materialized view created.
-- then Oracle automatically creates a table names test_tab in the ..._objects data dictionary view:
SCOTT@orcl_11gR2> select object_type, status, object_name
2 from user_objects
3 where object_name = 'TEST_TAB'
4 /
OBJECT_TYPE STATUS OBJECT_NAME
------------------- ------- ------------------------------
TABLE VALID TEST_TAB
MATERIALIZED VIEW VALID TEST_TAB
2 rows selected.
] 
Blog
Search
Help
Members
Register
Login
Home
I was unaware about this fact. I know that materialized view is stored physically, but don't know that it is stored in form of table.