oracledba.help
DataPump

Export\Import: Schema

Considerations

On the destination system you do not need to precreate the schema\user account(s). It will be created upon importing with the same password and specs as on the source database.

You can use both a full database export and schema export for the source of a schema import.

Destination Database Prerequisites

  1. Is taking the database out of ArchiveLogMode temporarily to improve import speed warranted?
    Otherwise consider using: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
  2. All the tablespaces the user(s) access exist.
    Though they would be recreated in the import, it is better to pre-create with the ideal location and size.
  3. All user tablespaces are sized adequately.
    You can use ts.createResizeCmds.sql
  4. The users Temporary tablespace exists and is large enough.
  5. All apps and processes on the destination schema have been stopped.
  6. Kill any user sessions that may access schema to be refreshed.

For a new database you might want to perform a full database import first before performing schema imports. This can ensure objects that span multiple schema are accounted for and the like.

Command Line

Export

expdp user_name/password 
  DIRECTORY=dir_obj_name 
  DUMPFILE=dumpfile_name.dmp 
  JOB_NAME=job_name 
  SCHEMAS=list_of_schema_to_export
  [COMPRESSION=type]
 expdp system/password DUMPFILE=scott.dmp DIRECTORY=datapump 
   JOB_NAME=xSchema LOGFILE=xSchema.log SCHEMAS=scott 

Import

impdp user_name/password 
  DIRECTORY=dir_obj_name 
  DUMPFILE=dumpfile_name.dmp 
  JOB_NAME=job_name 
  SCHEMAS=list_of_schema_to_import
 impdp system/password DUMPFILE=scott.dmp DIRECTORY=datapump
   JOB_NAME=impSchema LOGFILE=impSchema.log SCHEMAS=scott 

Parameter File (.par)

Export

# expdp system/password PARFILE=exportSchema.par
COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=scott.dmp
JOB_NAME=xSchema
LOGFILE=xSchema.log
SCHEMAS=SCOTT

Import

# impdp system/password PARFILE=importSchema.par
DIRECTORY=datapump
DUMPFILE=scott.dmp
JOB_NAME=impSchema 
LOGFILE=impSchema.log
SCHEMAS=SCOTT
TABLE_EXISTS_ACTION=replace

<- DataPump