Search My Oracle Blog

Custom Search

16 March, 2009

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

7 comments:

Anonymous said...

if i had already some data in table mv_2 and i create mv_2 materialized view on it, so oracle will overwrite my existing table data with mv_2 materialized view table data?

Regards!

Hemant K Chitale said...

If you have existing data in the table, the MV definition uses that data.

Also see http://hemantoracledba.blogspot.com/2009/03/materialized-view-on-prebuilt-table.html

Anonymous said...

Thanks Hemant , U saved me a lot of time ..

ron said...

thanks hemant..that helped

Anonymous said...

Thank Hemant very much!
it's very easy to understand!

Anonymous said...

HI Hemant,

I have one question :
My GSS is failing with the error
Error:-
#1: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.GL_ACCT_WISE_DATE_WISE_SUM_MV***ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired***

The table which is being refered is MVIEW table and there is always lock on this table

Could you please help me on the same.

Thanks in advance

Hemant K Chitale said...

Anonymous,
I don't see why a Gather_%_Stats would raise an ORA-54 against an MV table. An ORA-54 error occurs generally when you attempt DDL against an object with active DML.

An MV table is "locked" (in the sense it prevents DDL) when it is being updated by a Refresh.

Hemant

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016