How to export a single schema from Oracle EE to Oracle XE

datapumporacleoracle-11goracle-12coracle-xe

I would like to export a schema (DDL or DDL+data) from Oracle EE (Enterprise Edition) to Oracle XE (Express Edition) for development purpose (in our case: for use as test databases for Java developers).

The source schema contains some features, that are not available in Oracle XE, but none of them is required in development. A target schema can be modified to be compatible with XE.

In particular:

  • partition declarations can be omitted,
  • storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables),
  • tablespace declarations omitted or replaced (a single tablespace used in XE),
  • materialized views with some advanced features can be simplified or replaced with simple views,
  • jobs can be omitted,
  • java functions (scarcely used but present in one pl/sql package) can be disabled or imported with compilation errors.

One way to achieve it is to export source DDL to an sql script and then modify it by hand. But I'm curious if you see any other, more efficient way.

  • Oracle version: 11g / 12c
  • size of an sql file containing DDL: 7MB

Note: There already exists a similar question, but in my case I accept some differences between source and target schema.

Best Answer

There is no such one command to do this kind of migration of a schema because as you mentioned, Oracle Express edition doesn't support all the features that are used in the Enterprise edition. However, I tried to collect some of the useful parameters which can be used during expdp/impdp to modify or include/exclude (un)wanted objects and features.

  • partition declarations can be omitted.

    • In that case you can use PARTITION_OPTIONS parameter in impdp.

    PARTITION_OPTIONS=[NONE | DEPARTITION | MERGE].

    PARTITION_OPTIONS

  • storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables).

    • TRANSFORM parameter in impdp enables you to alter object creation DDL for objects being imported.

    TRANSFORM = transform_name:value[:object_type]

    TRNASFORM

  • tablespace declarations omitted or replaced (a single tablespace used in XE).

    • You can use REMAP_TABLESPACE parameter during impdp on Oracle XE instance.

    REMAP_TABLESPACE=source_tablespace:target_tablespace

    REMAP_TABLESPACE

  • materialized views with some advanced features can be simplified or replaced with simple views.

    • I think you need to import DDL of such materialized views into a SQL FILE then create view manually.

  • jobs can be omitted.

    • You can use EXCLUDE parameter to exclude jobs but currently, Data Pump Export/Import can exclude/include jobs which were created with DBMS_JOB.

    Reference

  • java functions (scarsely used but present in one pl/sql package) can be disabled or imported with compilation errors.

    • You can use EXCLUDE to ignore such java classes.

    EXCLUDE=JAVA_CLASS

    Now you can combine these parameters to form your expdp/impdp command.

    Note: All included reference links are related to Oracle 11g version, please refer to the Oracle documentation related to your version.

    Related Question