Inter-database import and exports using Oracle data pump

To move an entire schema from one schema to another within the same Oracle database, there is no need to export data to disk and then reimport it into the other schema. It can all be done using data pump network mode.

Create a loopback database link, meaning a database link pointing to the same database:

CREATE DATABASE LINK loopback_dblink
CONNECT TO system IDENTIFIED BY secretpassword
USING 'proddb01';

Then, precreate the schema JIM and create a separate tablespace for the schema:

CREATE BIGFILE TABLESPACE JIM_TABSPC
DATAFILE 
  '/data/oradata/proddb01/jim_tabscp.dbf' SIZE 9G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


CREATE USER JIM
  IDENTIFIED BY jimsseecretpassword 
  DEFAULT TABLESPACE JIM_TABSPC
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_USER
  ACCOUNT UNLOCK;

GRANT CONNECT TO JIM;
GRANT RESOURCE TO JIM;
ALTER USER JIM DEFAULT ROLE ALL;

GRANT UNLIMITED TABLESPACE TO JIM;

ALTER USER JIM QUOTA UNLIMITED ON JIM_TABSPC;

Finally, execute the data pump script that will clone the data in schema SCOTT to schema JIM:

impdp system/secretpassword \
    schemas=SCOTT \
    network_link=loopback_dblink \
    remap_schema=SCOTT:JIM \
    remap_tablespace=SCOTT:JIM \
    directory=DATA_PUMP_DIR \
    logfile=scott_to_jim_clone.log \
    transform=OID:N \
    transform=SEGMENT_ATTRIBUTES:N