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.
.
.