Thursday, September 15, 2011

DBA_Objects shows identical object on same schema?

MATERIALIZED VIEW
=================

Whenever a Materialized view created, you should see 2 entries in dba_objects with same name. Because, it creates a table(segment) and a materialized view.
So, We should NOT get confused that why 2 objects has same name on same schema.

SQL> create materialized view mv1 as select * from i;
Materialized view created.

SQL>select object_name,object_id,data_object_id,object_type,status from dba_objects where object_name='MV1'

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
------------------------------ ---------- -------------- ------------------- -------
MV1 4838577 4838577 TABLE VALID
MV1 4838579 MATERIALIZED VIEW VALID


SQL> alter MATERIALIZED VIEW MV_MOVEMENT_PROCESSES compile;

SQL> execute dbms_mview.refresh('MINE1.MV_MOVEMENT_PROCESSES');
PL/SQL procedure successfully completed.

No comments: