Usually, we choose to export the database into multiple dump files rather than a single dump file is because there are hardware limitations such as hard disk space constraints and limited network bandwidth when moving the dump files to a different backup locations.
This will not delve into multiple dump files in different locations due to disk space constraints. Basically, I cover only multiple dump files in the same location for easy copying over a limited network bandwidth.
Even though it is possible to configure a network drive location in Oracle and export straight to that location, it is highly not recommended because it places a huge amount of stress on Oracle itself. It is best to export the dump files to a local drive and let the file transfer over the network handled by the operating system.
In order to export a schema and split the dump file into smaller size files, we specify the filesize parameter:
Syntax:
expdp 'sys/pwd as sysdba' directory=dumpdir dumpfile=mydump-%u.dmp filesize=500M logfile=mydump.log schemas=schema1,schema2
%u is the substitution variable for the number of dump files generated. Large file transfer over the network could use up a lot of RAM and might cause the system to hang.
Importing the dump files is straight forward as follow:
impdp 'sys/pwd as sysdba' directory=dumpdir dumpfile=mydump-%u.dmp logfile=imp.log remap_schema=schema1:newschema1,schema2:newschema2
The remap_schema parameter is optional and can be omitted if you are importing back to the same schema. You do not need to pre-create the schema (db user) if it does not already exist, though you could. If a schema is remapped to a different name that does not already exist, the password (db user password) remains the same as the original schema.
If the original schema is assigned to a different tablespace, the import syntax above does not create the tablespace. Instead, the schema will be created on the default tablespace, USERS. If you need the schema to use a different tablespace, you must pre-create the schema before importing.
This will not delve into multiple dump files in different locations due to disk space constraints. Basically, I cover only multiple dump files in the same location for easy copying over a limited network bandwidth.
Even though it is possible to configure a network drive location in Oracle and export straight to that location, it is highly not recommended because it places a huge amount of stress on Oracle itself. It is best to export the dump files to a local drive and let the file transfer over the network handled by the operating system.
In order to export a schema and split the dump file into smaller size files, we specify the filesize parameter:
Syntax:
expdp 'sys/pwd as sysdba' directory=dumpdir dumpfile=mydump-%u.dmp filesize=500M logfile=mydump.log schemas=schema1,schema2
%u is the substitution variable for the number of dump files generated. Large file transfer over the network could use up a lot of RAM and might cause the system to hang.
Importing the dump files is straight forward as follow:
impdp 'sys/pwd as sysdba' directory=dumpdir dumpfile=mydump-%u.dmp logfile=imp.log remap_schema=schema1:newschema1,schema2:newschema2
The remap_schema parameter is optional and can be omitted if you are importing back to the same schema. You do not need to pre-create the schema (db user) if it does not already exist, though you could. If a schema is remapped to a different name that does not already exist, the password (db user password) remains the same as the original schema.
If the original schema is assigned to a different tablespace, the import syntax above does not create the tablespace. Instead, the schema will be created on the default tablespace, USERS. If you need the schema to use a different tablespace, you must pre-create the schema before importing.
