Problems creating MVIEW DDL using Data Pump and the SQLFILE option

Background

Data Pump can easily generate object DDL by importing using the SQLFILE option.  And typically we think that Data Pump simply makes calls to DBMS_METADATA.GET_DDL just like DBA or normal user would.

However in some situations this is not the case.  One particular example is with Materialized View DDL as of Oracle 11.2.0.3.  The DDL that Data Pump generates uses a special interal syntax and "USING" clause.  The resulting DDL can't be manually executed via sqlplus and gives the misleading error message:

ERROR at line 1:
ORA-00942: table or view does not exist

This new behaviour is explained in My Oracle Support (MOS) Note "Mview Create DDL Statement From Impdp with Sqlfile Option (Doc ID 1528826.1)".  However the following section explains the problem in more detail for the benefit of a more thourough understanding or for those without MOS access.

Example

The problem is simple to replicate.  First create a sample materialized view:

SQL> connect scott/tiger
Connected.
SQL> create materialized view my_mview as select * from dba_objects;

Materialized view created.

SQL>

Next use Data Pump to export the materialized view, and import using the SQLFILE option:

$ expdp dumpfile=demo.dmp nologfile=true reuse_dumpfiles=true include=MATERIALIZED_VIEW userid=scott/tiger

Export: Release 11.2.0.3.0 - Production on Mon Jan 20 10:40:03 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": dumpfile=demo.dmp nologfile=true reuse_dumpfiles=true include=MATERIALIZED_VIEW userid=scott/********
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/ORCL/dpdump/demo.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:40:19

$ impdp dumpfile=demo.dmp nologfile=true full=y sqlfile=demo.sql userid=scott/tiger

Import: Release 11.2.0.3.0 - Production on Mon Jan 20 10:40:26 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_FULL_01": dumpfile=demo.dmp nologfile=true full=y sqlfile=demo.sql userid=scott/********
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 10:40:30

$

Review the SQL that was generated from the SQLFILE option:

$ cat demo.sql
-- CONNECT SCOTT
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/MATERIALIZED_VIEW
CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") USING ("MY_MVIEW", (10, 'ORCL', 1, 0, 0, "SYS", "USER$", '2014-01-20 10:34:47', 0, 0, '2014-01-20 10:34:47', '', 0, 337494, 0, NULL), 2097472, 10, ('2014-01-20 10:34:47', 1, 0, 0, 337494, 33554432, 0, 0, 2, NULL, NULL)) REFRESH FORCE AS select * from dba_objects;

ALTER MATERIALIZED VIEW "SCOTT"."MY_MVIEW" COMPILE;

Notice in the above generated DDL that the special "USING" syntax is used.  Try running the generated DDL in sqlplus and we will receive the misleading "ORA-00942: table or view does not exist" error:

SQL> connect scott/tiger
Connected.
SQL> drop materialized view my_mview;

Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") USING ("MY_MVIEW", (10, 'ORCL', 1, 0, 0, "SYS", "USER$", '2014-01-20 10:34:47', 0, 0, '2014-01-20 10:34:47', '', 0, 337494, 0, NULL), 2097472, 10, ('2014-01-20 10:34:47', 1, 0, 0, 337494, 33554432, 0, 0, 2, NULL, NULL)) REFRESH FORCE AS select * from dba_objects;

CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") USING ("MY_MVIEW", (10, 'ORCL', 1, 0, 0, "SYS", "USER$", '2014-01-20 10:34:47', 0, 0, '2014-01-20 10:34:47', '', 0, 337494, 0, NULL), 2097472, 10, ('2014-01-20 10:34:47', 1, 0, 0, 337494, 33554432, 0, 0, 2, NULL, NULL)) REFRESH FORCE AS select * from dba_objects
*
ERROR at line 1:
ORA-00942: table or view does not exist

If we try to actually import the materialized view using the Data Pump impdp utility we (unfortunately) can experience the exact same error:

$ impdp dumpfile=demo.dmp nologfile=true full=y userid=scott/tiger

Import: Release 11.2.0.3.0 - Production on Mon Jan 20 11:11:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": dumpfile=demo.dmp nologfile=true full=y userid=scott/********
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") USING ("MY_MVIEW", (10, 'ORCL', 1, 0, 0, "SYS", "USER$", '2014-01-20 10:34:47', 0, 0, '2014-01-20 10:34:47', '', 0, 337494, 0, NULL),
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:12:10

The workaround is to manually use the DBMS_METADATA.GET_DDL package instead of relying on the DDL generated from Data Pump.  Using DBMS_METADATA.GET_DDL produces properly structured syntax:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

PL/SQL procedure successfully completed.

SQL> set long 50000 pages 999
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MY_MVIEW','SCOTT') from dual;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MY_MVIEW','SCOTT')
--------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJEC
T_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME
", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "E
DITION_NAME")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from dba_objects;


SQL>

And of course the DDL generated above can indeed be executed successfully in sqlplus:

SQL> drop materialized view my_mview;

Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW "SCOTT"."MY_MVIEW" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME")
2 ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
3 NOCOMPRESS LOGGING
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
6 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
7 TABLESPACE "USERS"
8 BUILD IMMEDIATE
9 USING INDEX
10 REFRESH FORCE ON DEMAND
11 USING DEFAULT LOCAL ROLLBACK SEGMENT
12 USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
13 AS select * from dba_objects;

Materialized view created.

SQL>
 

Implications

Data Pump exports are commonly used as a way of backing up DDL for future reference or use in secondary databases.  However it must be noted that for materialized views at least the DDL that Data Pump generates may actual not be useable.  If materialized view definitions need to be backed up, do so using an alternative mechanism such as RMAN or DBMS_METADATA.GET_DDL.

Versions Affected

Oracle 11.2.0.3 and greater.


Copyright & Disclaimer

Copyright© 2014 Strategic Database Solutions Inc. All rights reserved.  All material on this site is 100% original material and is the property of Strategic Database Solutions Inc. unless explicitly stated otherwise.  Product and company names mentioned in this website may be the trademarks of their respective owners.  All information provided is offered in good faith in the spirit of collaboration and knowledge sharing but is not guaranteed to be accurate or correct.  Use all tips, suggestions, and advice at your own risk.