This is a brief introduction to how to export and import table structure definitions using the datapump tool of the ORACLE database.Because business scenarios can be very simple, you won't encounter any problems, or business scenarios can be very complex, and you will encounter a lot of problems.Here's a brief description of what you need.A batch of tables is ready for archiving. The strategy of archiving is to archive a batch of tables T1, T2,,,, Tn under UserA under UserB (UserB.T1, UserB.T2....). Then such a batch of tables must be created under UserB instead of being manually created.Manually created, time consuming and laborious, you want to export the definitions of these tables in batch using the datapump tool, then import them in batch.

Correct practice:

1: Export table structure definition first

expdp xxxx/xxxx tables=xxx,xxx,xxx..... directory=dumpdir  content=metadata_only dumpfile=test.dmp logfile=exp_test_20200527.log;

2: There are several things to do when importing table structure definitions.

2.1 The original table is in a different SCHEMA from the target table, so this conversion must be completed when importing, using REMAP_The SCHEMA parameter transfers tables and data from one SCHEMA to another, similar to the fromuser and touser parameters in the old imp tool, where the fromuser and touser parameters are merged into REMAP_SCHEMA parameter.How to use this parameter REMAP_SCHEMA=source_schema:target_schema.

Important: Even if the corresponding SCHEMA you specified does not exist, the metadata of CRATE USER in the DUMP file will be used to create a corresponding user as long as the connected user has sufficient privileges when importing.

2.2 In general, there is a default table space for the corresponding SCHEMA (User), which may be different from the default table space for SCHEMA, so the parameter REMAP_must be usedTABLESPACE solves the problem of importing data to change tablespaces, otherwise you will encounter the error ORA-01950: no privileges on tablespace'xxxx'.

Important: It is possible that the data and indexes of the original table are in different tablespaces, so you may need to match multiple tablespaces, using REMAP_TABLESPACE=src1:dst1 REMAP_TABLESPACE=src2:dst2

2.3 Logical conflict, which is not the relationship between tools but is due to logical relationship.

As shown in the following case, for example, the original table "TEST"."INV_CARTONS "Follow" TEST"."INV_STORE_CODES "has a primary foreign key relationship.But we did not export or import "TEST"."INV_STORE_CODES ", then there is no" TEST_at this timeARCH'.'INV_STORE_CODES ", so an error was thrown.

$ impdp xxx/xxx tables=test.inv_cartons,test.inv_month_end_aging directory=dumpdir  remap_schema=test:test_arch REMAP_TABLESPACE=TEST_DATA:TEST_DATA_ARCH REMAP_TABLESPACE=TEST_IDX:TEST_DATA_ARCH dumpfile=test.dmp logfile=imp_test_20200527.log

Import: Release 10.2.0.5.0 - 64bit Production on Wednesday, 27 May, 2020 13:57:10

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=test.inv_cartons,test.inv_month_end_aging directory=dumpdir remap_schema=test:test_arch REMAP_TABLESPACE=TEST_DATA:TEST_DATA_ARCH REMAP_TABLESPACE=TEST_IDX:TEST_DATA_ARCH dumpfile=test.dmp logfile=imp_test_20200527.log 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "TEST_ARCH"."INV_CARTONS" ADD CONSTRAINT "FK_INV_CART_FK_INV_CA_INV_STOR" FOREIGN KEY ("STORE_CD") REFERENCES "TEST_ARCH"."INV_STORE_CODES" ("STORE_CD") ENABLE NOVALIDATE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:57:11

Common examples of datapump tool import scripts

impdp xxx/xxx tables=xxxxxx directory=dumpdir  remap_schema=xxxxx:xxxxx REMAP_TABLESPACE=xxxx:xxxx REMAP_TABLESPACE=xxxx:xxxx dumpfile=test_20200527.dmp logfile=imp_test_20200527.log