Thursday, January 29, 2015

Can a table and a materialized view have the same name in one schema in a DB?

A materialized view is a table, therefore you'd expect to see both of the entries in DBA_OBJECTS. If you have created a materialized view in a schema, then you can't create a table in the same schema with the same name as the materialized view.

Code:
SQL> drop materialized view test_master;

Materialized view dropped.

SQL> drop table test_master;
drop table test_master

           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE MATERIALIZED VIEW test_master

  2     BUILD IMMEDIATE
  3     AS SELECT object_name, object_type
  4     from dba_objects;

Materialized view created.

SQL> select owner,
  2     object_name,
  3     object_type,
  4     status
  5  from dba_objects
  6  where object_name='TEST_MASTER';

OWNER    OBJECT_NAME                    OBJECT_TYPE         STATUS
-------- ------------------------------ ------------------- -------
TEST     TEST_MASTER                    TABLE               VALID
TEST     TEST_MASTER                    MATERIALIZED VIEW   VALID

SQL> create table test_master (a1 number);
create table test_master (a1 number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

No comments:

Post a Comment