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 🙂
Learn. Share. Repeat. 