Oracle imports and exports data using IMPDP and EXPDP

1. Exporting data

Before you start exporting data, create a directory, because you need to specify directory when you import, and the exported dump files and logs are saved in the directory corresponding to that directory

SQL> create directory dumpdir as ‘D:\ora\dump‘;

Dumpdir is the directory name, as specifies the local directory corresponding to the directory.

Here I import all the table data of the Shiot user

expdp shiot/123456 directory=dumpdir dumpfile=shiot.dump logfile=shiot.log

Note EXPDP is not executed under the SQL command line, where dumpfile specifies the exported dump file name, logfile specifies the log file name.

If you only need to export some of the table data for a Shiot user, you can specify which tables you want to export by using the tables parameter

expdp shiot/123456 directory=dumpdir dumpfile=shiot.dump logfile=shiot.log tables=test1,test2

Tables specify the table to be exported, there can be multiple, here I export test1 and test2 table

2. Import data

Using IMPDP to import data, be careful to place the dump file you want to import into the directory specified by directory.

Assuming that the imported user already exists, execute the following command to import the data

impdp shiot/123456 directory=dumpdir dumpfile=shiot.dump  table_exists_action=append   # table_exists_action指定表存在时的动作，append表示追加内容

If the table space is different when the data import is exported, you need to do a mapping

impdp shiot/123456 directory=dumpdir dumpfile=shiot.dump  table_exists_action=append remap_tablespace=iot_out:iot    #其中iot_out是导出数据时的表空间，iot是导入数据的表空间

Mapping is also required if the user who imported the export is different

impdp shiot/123456 directory=dumpdir dumpfile=shiot.dump  table_exists_action=append remap_schema=shiot_log:shiot   #其中shiot_log是导出用户，shiot是导入用户

Oracle imports and exports data using IMPDP and EXPDP