Expdp and impdp practices

We often use the exp/imp command for import and export, which is indeed convenient, but also limited. For example, the tablespace names to be imported and exported must be the same, and the schema names must be the same.

Recently I encountered such a problem. In this case, the Data Pump Import and Export tools such as expdp and impdp play a role.

Space name of the table and index to be exported:

SQL> select distinct tablespace_name from user_tables;
TABLESPACE_NAME
---------------
QXU_IMC_DATA

SQL> select distinct tablespace_name from user_indexes;
TABLESPACE_NAME
---------------
QXU_IMC_DATA
QXU_IMC_INDEX
INIT_IMC_DATA

1. Export

First, create the dumpfile path:

Create directory test_dump = 'C:/dump ';

Export: expdp user/pwd directory = test_dump dumpfile = qxuimc. dmp schemas = qxuimc logfile = exp_qxuimc.log

Under C:/dump, the exported dmp file and log file are found.

2. Import

Place the dmp file on the database server to be imported.

Similarly, if you have not created a dumpfile path before, you also need to create it.

Impdp user/pwd DIRECTORY = test_dump DUMPFILE = qxuimc. dmp REMAP_SCHEMA = qxuimc: *** LOGFILE = imp_test.log REMAP_TABLESPACE = QXU_IMC_DATA: ***, QXU_IMC_INDEX: ***, INIT_IMC_DATA :***

REMAP_SCHEMA is used to map the exported schema to a new name. REMAP_TABLESPACE indicates to map the exported tablespace to a new name.

Note:

1. dumpfile can be viewed using select * from dba_directories.

2. Before import, you must delete all objects that are the same as the import object, such as function, package, table, index, sequence, and role. Otherwise, the import of this part of content will be ignored during import, record errors to logs.

3. There are still many parameters for impdp and expdp. Here we only use the most common parameters, and there are also a lot of information on the Internet.

4. About the version of import and export, I wrote an article about:

Http://blog.csdn.net/bisal/article/details/17350155

Export client compatibility: Always use a version of the EXPORT utility that is equal to the lowest version of either the source or the target database. EXPORT client compatibility: We recommend that you use the same EXPORT tool version as the lowest version in the source or target database.
Import client compatibility:
Always use a version of the IMPORT utility that is equal to the version of the target database. IMPORT client compatibility: We recommend that you use the same IMPORT tool version as the target database version.

The exported version is 10 GB and the imported version is 11 GB. Expdp uses 10 Gb, impdp uses 11 GB, and complies with the above standard.

In this case, expdp supports the version parameter to specify the version of the target database.