Materialized Views and Tables

Responding to a recent forums posting on a Materialized View having the same name as a Table, here I demonstrate the case.

In this example below, MV_1 is created as a Materialized View. Oracle automatically creates a table with the same name as the underlying table. This is for the storage of the data that the Materialized View, by definition, is supposed to be physically present .
When I later drop MV_1, the underlying table also gets dropped.

In the second case, MV_2 is actually a table. With the table pre-existing, I create a Materialized View on the Prebuilt Table. If I drop the Materialized View, table remains -- because it predates the MV definition.

SQL> connect test_user/test_user
Connected.
SQL> create table source_tb_1 (col_1 varchar2(5));

Table created.

SQL> create materialized view mv_1 as select * from source_tb_1;

Materialized view created.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV_1                           TABLE
MV_1                           MATERIALIZED VIEW
SOURCE_TB_1                    TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_1

SQL> drop materialized view mv_1;

Materialized view dropped.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SOURCE_TB_1                    TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1

SQL> create table mv_2 (col_1 varchar2(5));

Table created.

SQL> create materialized view mv_2 on prebuilt table as select * from source_tb_1;

Materialized view created.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV_2                           TABLE
MV_2                           MATERIALIZED VIEW
SOURCE_TB_1                    TABLE

SQL> select table_name from user_tables ;       

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_2

SQL> drop materialized view mv_2;

Materialized view dropped.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV_2                           TABLE
SOURCE_TB_1                    TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_2

SQL>  

Thus, "MV_1" appears as both a Table and a Materialized View. Dropping the Materialized View results in the table being dropped. Conversely, "MV_2" is actually a Table with a Materialized View being defined over it. Dropping the Materialized View only drops the MV definition, the table remains.
.
.