How to replace and overwrite all existing objects in Oracle with impdp for full database import

• You already have a database, so you don't need the 'create database'.

• Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set.

• When you create a user, don't use that @localhost construct. Your simple create user foo identified by bar is the correct syntax, but as @a1ex07 pointed out, you need to be attached to the pluggable database, not the container. This is a drastically new architecture for Oracle, beginning with 12c. You need to spend a bit of time with the 12c Concepts manual (see my link above), especially Part VI - Mutlitenant Architecture.

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.