Use the data pump to restore the specified tables and sequences to a new schema

Use the data pump to restore the specified tables and sequences to a new schema

Create test users first

--Create the user

Create user SCOTT

default tablespace USERS

temporary tablespace TEMP

profile DEFAULT

password expire

account lock;

--Grant/revoke role privileges

Grant connect to SCOTT;

Grant resource to SCOTT;

--Grant/revoke System privileges

Grant Unlimited tablespace to SCOTT;

alter user Scott identified by Scott

--Create the user

Create user hr identified by hr

default tablespace USERS

temporary tablespace TEMP

profile DEFAULT

password expire

account lock;

--Grant/revoke role privileges

Grant connect to hr;

Grant resource to hr;

--Grant/revoke System privileges

Grant Unlimited tablespace to hr;

View object information under Scott

Export Scott

EXPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017$rq.dmp logfile=expdp_scott_1017$rq.log schemas= Scott

Import the specified table

Test 1: Using tables

Import the TA1,TA2,TA3 under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= SCOTT:HR tables=scott. Ta1,scott. Ta2,scott. TA3;

To view objects under import HR

Purge objects that import hr

Test 2: Using the Include

This import will prompt Ora-31655:no data or metadata objects selected for job

Unable to import table, need to test 1 of tables Import

Import the TA1 under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=TABLE:\ "=\ ' scott.ta1\ ' \";

Import the TA1,TA2,TA3 under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=TABLE:\ "in \ (\ ' ta1\ ', \ ' ta2\ ', \ ' ta3\ ' \) \";

Import the specified sequence

You need to use include to import the development columns, and you need to use the escape character "\"

Import the seq_ta1_id under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=sequence:\ "in \ (\ ' seq_ta1_id\ ' \) \";

Import the seq_ta2_id,seq_ta3_id under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=sequence:\ "in \ (\ ' seq_ta2_id\ ', \ ' seq_ta3_id\ ' \) \";

Viewed two imported objects, all successfully

Import all the sequences under Scott into HR:

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= SCOTT:HR include=sequence;

To view imported objects

When importing an object specifying a prefix is also unable to import

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=SEQUENCE:\ "in \ (\ 'Scott. seq_ta1_id\ ' \) \ ";

The following attempts to import the table and sequence together, but neither succeeds, only the sequence is imported, so the current separately imported

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=SEQUENCE:\ "in \ (\ ' seq_ta2_id\ ', \ ' seq_ta3_id\ ' \) \" Tables=scott. Ta1,scott. Ta2,scott. TA3;

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= SCOTT:HR tables=scott. Ta1,scott. Ta2,scott. TA3 include=SEQUENCE:\ "in \ (\ ' seq_ta2_id\ ', \ ' seq_ta3_id\ ' \) \";

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=SEQUENCE:\ "in \ (\ ' seq_ta2_id\ ', \ ' seq_ta3_id\ ' \) \", Include=TABLE:\ "in \ (\ ' ta1\ ', \ ' ta2\ ', \ ' ta3\ ') \ "

IMPDP system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema= Scott:hr include=SEQUENCE:\ "in \ (\ ' seq_ta2_id\ ', \ ' seq_ta3_id\ ' \) \",TABLE:\ "in \ (\ ' ta1\ ', \ ' ta2\ ', \ ' ta3\ ' \) \ "

A case of IMPDP ORA-31655 error handling

http://www.oracleonlinux.cn/2012/02/impdp-ora-31655/

Ora-39001:invalid argument value Ora-39071:value for EXCLUDE is badly formed.

http://www.codeweblog.com/ora-39001-invalid-argument-value-ora-39071-value-for-exclude-is-badly-formed/

The use of exclude/include in EXPDP IMPDP

http://blog.csdn.net/liqfyiyi/article/details/7248911

Use the data pump to restore the specified tables and sequences to a new schema