ORACLE EXPDP export/Import all tables beginning with XX

Scenario: There are 2 Oracle users, usera default tablespace tablespacea, userb default Tablespace tablespaceb.

Now you need to export the system tables in UserA (the table that starts with SYS in the user tablespace) to UserB, overwriting the UserB system tables without overwriting UserB's business tables. Here's how:

Export the table starting with SYS in the UserA user schema, using tables=sys%

EXPDP Usera/password Directory=tmp_bak dumpfile=usera-170504-expdp-tab.dmp logfile=usera-170504-expdp-tab.log tables=sys%

If you only need to export a few tables, use Tables=table1,table2

EXPDP Usera/password Directory=tmp_bak dumpfile=usera-170504-expdp-tab02.dmp logfile=usera-170504-expdp-tab02.log Tables=table1,table2

Import the exported DMP file into the schema of the UserB user.

It is important to note that the tablespace that the schema corresponds to the table has changed and that the SYS table already exists in the table space of the original UserB. Therefore, the following parameters should be added when importing:

①REMAP_SCHEMA Specifies the source schema and the destination schema.

②REMAP_TABLESPACE Specifies the source tablespace and destination tablespace.

③table_exists_action=replace overwrites a table with duplicate names.

Before importing, be careful to back up the data of UserB, avoid the data loss caused by export error.

EXPDP Userb/password Directory=tmp_bak dumpfile=userb-170504-expdp.dmp logfile=userb-170504-expdp.log

Formally import the table beginning with SYS into the UserB user.

IMPDP userb/password directory=tmp_bak dumpfile=usera-170504-expdp-tab.dmp remap_schema=usera:userb REMAP_ Tablespace=tablespacea:tablespaceb Table_exists_action=replace Logfile=userb-0504-impdp-tab.log

This article comes from "Look back in a year" blog, make sure to keep this source http://cubix.blog.51cto.com/7251166/1922222

ORACLE EXPDP export/Import all tables beginning with XX