This is the 10th day of my participation in the August More Text Challenge

Introduce a,

Data pump (EXPDP, IMPDP) is a new technology introduced in Oracle 10G. It is compatible with most of the functions of the previous data export and import tools (EXP, IMP) and has been further improved to provide many new functions to meet complex business requirements. Different from traditional exp and IMP tools, data pump commands need to be executed on the database server.

Data pumps are logical migrations across operating system versions and database versions. An earlier version is compatible with an earlier version. Data of an earlier version is exported to an earlier version. Add the version number of the earlier version when exporting data.

Second, the use of

Environment:

Operating system version + Database version + Database character set

Redhat 5.6 + Oracle 10.2.0.5 + AMERICAN_AMERICA ZHS16GBK

Windows2008 + Oracle11.2.0.4 + SIMPLIFIED CHINESE_CHINA ZHS16GBK

The database character sets of derivative data must be consistent; otherwise, garbled characters may occur.

Character set query command

select userenv('language') from dual;
Copy the code

2.1 Export Create an import directory

The source end exports the directory, and the destination end imports the directory.

Create directory expdp as '/home/oracle/expdp'; Create directory impdp as 'D:\impdp'; Set pagesize 200 set line 200 col DIRECTORY_PATH for A60 col OWNER for A10 select * from dba_directories; Grant read,write on directory expdp to Syd; grant directory expdp to Syd; grant read,write on directory impdp to scott;Copy the code

2.2 Exporting Imported Data

Perform the following operations to export data from the source end and import data from the target end.

2.2.1 Export and import by Table

Expdp Syd/Oracle dumpfile=expdp_test_20200811.dmp directory=expdp tables=syd.test Logfile =expdp_test_20200811.log exclude=statistics Impdp Syd/Oracle dumpfile=expdp_test_20200811.dmp directory=impdp logfile=expdp_test_20200811.log Impdp Scott/Oracle dumpfile=expdp_test_20200811.dmp directory=impdp remAP_schema = Syd: Scott remap_tablespace=syd:users logfile=expdp_test_20200811.logCopy the code

2.2.2 Import by User

Expdp Syd/Oracle Dumpfile = expdp_SYdt % u_20200811.dMP directory=expdp schemas= Syd parallel=2 Logfile =expdp_syd_20200811.log exclude=statistics Impdp Syd/Oracle Dumpfile =expdp_sydt% u_20200811.dmp directory= IMPdp parallel=2 logfile= IMPdp_syd_20200811.log -- Import (with the source user, Impdp Scott/Oracle Dumpfile =expdp_sydt% u_20200811. DMP directory=impdp remAP_SCHEMA = Syd: Scott remap_tablespace=syd:users parallel=2 logfile=impdp_syd_20200811.logCopy the code

2.2.3 Guide table structure only

Expdp Syd/Oracle dumpfile=expdp_test_20200811.dmp directory=expdp Tables =syd.test logfile=expdp_test_20200811.log content=metadata_only exclude=statistics -- Export EXPDP Syd/Oracle by user dumpfile=expdp_sydt%U_20200811.dmp directory=expdp schemas=syd parallel=2 logfile=expdp_syd_20200811.log Content = metadatA_only exclude=statistics -- Import impdp Syd/Oracle dumpfile=expdp_test_20200811.dmp directory=impdp Logfile =expdp_test_20200811.log -- Import impdp Syd/Oracle dumpfile=expdp_sydt% u_20200811.dmp directory= IMPdp parallel=2 logfile=impdp_syd_20200811.logCopy the code

2.2.4 Derivative data of higher version to lower version Database

Expdp Scott/Oracle Dumpfile = expdp_EMP_20200811.dmp directory= IMPdp Tables =scott.emp logfile= expdp_EMP_20200811. log version=10.2.0.5 exclude=statistics -- Import impdp Scott/Oracle dumpfile=EXPDP_EMP_20200811.DMP directory=expdp logfile=impdp_emp_20200811.logCopy the code

Collect statistical information

Run the database as the sys administrator. (Collect statistics for tables or users with a large amount of data; otherwise, SQL execution efficiency may be affected.)

Exec DBMS_stats. gather_TABLE_stats (OWnName => 'SYD', tabName => 'TEST', ESTIMate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE); Collect user statistics exec dbms_stats.gather_schema_stats(ownname=>'SYD',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');Copy the code