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
- Is taking the database out of ArchiveLogMode temporarily to improve import speed warranted?
Otherwise consider using: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y - 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. - All user tablespaces are sized adequately.
You can use ts.createResizeCmds.sql - The users Temporary tablespace exists and is large enough.
- All apps and processes on the destination schema have been stopped.
- 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