MVIEW creation on tables with “OBJECT TYPE”

Recently i faced the below error while creating the materialized view on the target database.

ORA-21700:object does not exist or is marked for delete
CREATE MATERIALIZED VIEW LAPTEST.USER_MASTER
        TABLESPACE LAP_MATERIALIZE_VIEW
        BUILD IMMEDIATE
        USING INDEX
        TABLESPACE LAPDATA
        REFRESH COMPLETE ON DEMAND
        WITH PRIMARY KEY
        AS
        SELECT * FROM UA_USER_MASTER@DB_LINK_LAPTEST_MFREP;
SELECT * FROM UA_USER_MASTER@DB_LINK_LAPTEST_MFREP
              *
ERROR at line 9:
ORA-21700: object does not exist or is marked for delete

This was the first time i had got some error while creating materialized view, but had no clue why “object does not exist or is marked for delete” error.

Immediately connected to the source database and checked for the table.

mmftrep >desc dba.ua_user_master
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------
 M_USER_ID                                            NOT NULL VARCHAR2(15)
 M_ENTITY_TYPE                                                 VARCHAR2(10)
 M_ENTITY_CODE                                                 VARCHAR2(15)
 M_ENABLE_STATUS                                      NOT NULL VARCHAR2(1)
 M_NOOF_LOGIN                                         NOT NULL NUMBER(2)
 M_TXN_ID                                             NOT NULL NUMBER
 M_BANNER_DISP_IND                                    NOT NULL VARCHAR2(1)
 M_NET_LOGIN_IND                                               VARCHAR2(1)
 M_NAME_INFO                                                   NAME_OBJECT
 M_ADMIN_IND                                                   VARCHAR2(1)

As per Oracle document

An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data.

Different types of materialized views:

Primary Key Materialized Views
Object Materialized Views
ROWID Materialized Views
Complex Materialized Views

As the referenced table “UA_USER_MASTER” on the source database had user defined datatype column “NAME_OBJECT”, the materialized view creation failed.

So lets try to create the materialized view on tables (in source) with user defined datatype columns.From Oracle document

When replicating user-defined types and the schema objects on which they are based, the following conditions apply:

a. The user-defined types replicated at the master site and materialized view site must be created at the materialized view site before you create any materialized views that depend on these types.

b. All of the masters on which a materialized view is based must be at the same master site to create a materialized view with user-defined types.

c. A user-defined type must be exactly the same at all replication sites:-
All replication sites must have the same object identifier (OID), schema owner, and type name for each replicated user-defined type.

If the user-defined type is an object type, then all replication sites must agree on the order and datatype of the attributes in the object type.

1. On the source database :-

SELECT TYPE_OID FROM DBA_TYPES WHERE TYPE_NAME = 'NAME_OBJECT';

TYPE_OID
--------------------------------
B7854A37B3C311D6A99D00508BFCBE98

2. Create the user-defined type on the destination database.All replication sites must have the same object identifier (OID), schema owner, and type name for each replicated user-defined type.

20:28:59 lap >CREATE OR REPLACE
20:28:59   2  TYPE "SYSTEM"."NAME_OBJECT"  OID 'B7854A37B3C311D6A99D00508BFCBE98'
20:28:59   3   as object  (
20:28:59   4     NAME_1_L                                 VARCHAR2(100),
20:28:59   5     NAME_2_L                                 VARCHAR2(100),
20:28:59   6     NAME_3_L                                 VARCHAR2(100),
20:28:59   7     NAME_4_L                                 VARCHAR2(100),
20:28:59   8     NAME_5_L                                 VARCHAR2(100),
20:28:59   9     NAME_6_L                                 VARCHAR2(100)
20:28:59  10    )
20:28:59  11  /

Type created.

3. Granted all the privileges as it was on the source database

20:29:05 lap >grant execute on NAME_OBJECT to public;

Grant succeeded.

4. Create the materialized view on the destination

CREATE MATERIALIZED VIEW LAPTEST.USER_MASTER
TABLESPACE LAP_MATERIALIZE_VIEW
AS 
SELECT M_USER_ID,M_ENTITY_TYPE,M_ENTITY_CODE,
M_ENABLE_STATUS,M_NOOF_LOGIN,M_TXN_ID,M_BANNER_DISP_IND,M_NET_LOGIN_IND,
c.M_NAME_INFO.NAME_1_L,
c.M_NAME_INFO.NAME_2_L,
c.M_NAME_INFO.NAME_3_L,
c.M_NAME_INFO.NAME_4_L,
c.M_NAME_INFO.NAME_5_L,
c.M_NAME_INFO.NAME_6_L,
M_ADMIN_IND
FROM UA_USER_MASTER@DB_LINK_LAPTEST_MFREP c;

Materialized view created.

Elapsed: 00:00:01.07

So, materialized view is finally created 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s