Exp/imp exporting the specified data from a production library table to a test library example

Generally speaking, Exp/imp is the previous generation export importer, EXPDP/IMPDP is a new generation of export importer program. For large data volume Export import preferred EXPDP/IMPDP, you can use the degree of parallelism, the table space and other operations are more flexible. For migrating small amounts of data, you can use Exp/imp for easier operation.

Requirements: EXP Export a library (11.2.0.3) ZJY user's partition table T_jingyu_part partial data, data table Space Dbs_d_jingyu, Index table space Dbs_i_jingyu.

IMP imports the default tablespace dbs_d_test for the test user under the B library (11.2.0.4) test user.

A library zjy the user to prepare the work:

' +data'+data '  size 20M autoextend off;create user zjy identified by zjy default Tablespace DBS _d_jingyu;

Create TableT_jingyu_part (ID Number, Deal_date Date,area_code Number, Contentsvarchar2(4000)) partition byRange (deal_date) (Partition P1ValuesLess Than (To_date ('2014-02-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P2ValuesLess Than (To_date ('2014-03-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P3ValuesLess Than (To_date ('2014-04-01','YYYY-MM-DD')) Tablespace dbs_d_jingyu,partition P4ValuesLess Than (To_date ('2014-05-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P5ValuesLess Than (To_date ('2014-06-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P6ValuesLess Than (To_date ('2014-07-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P7ValuesLess Than (To_date ('2014-08-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P8ValuesLess Than (To_date ('2014-09-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition p9ValuesLess Than (To_date ('2014-10-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P10ValuesLess Than (To_date ('2014-11-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P11ValuesLess Than (To_date ('2014-12-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P12ValuesLess Than (To_date ('2015-01-01','YYYY-MM-DD')) tablespace dbs_d_jingyu,partition P_maxValuesLess than (MaxValue) tablespace Dbs_d_jingyu);

Insert  intoT_jingyu_part (ID, deal_date, area_code, contents)SelectRowNum, To_date (To_char (sysdate-365,'J')+Trunc (Dbms_random.value (0,365)),'J'), Ceil (Dbms_random.value (590,599)), Rpad ('*', -,'*') fromDualconnect byRowNum<= 100000;Commit;

Create Index  on T_jingyu_part (ID, area_code) local tablespace Dbs_i_jingyu;

 select  count  (1 ) from  T_jingyu_part partition (P1); 
 select  count  (1 ) from  T_jingyu_part partition (P2); 

Select Count (1fromwhere>= to_date ('2014-11-11',' YYYY-MM-DD '  and <= to_date ('2014-12-12','yyyy-mm-dd' );

Exp ZJY/ZJY Parfile=exp.par

file=T_jingyu_part.dmplog=exp_t_jingyu_part.logtables=t_jingyu_partquery= " where Deal_date >= to_date (' 2014-11-11 ', ' yyyy-mm-dd ') and deal_date <= to_date (' 2014-12-12 ', ' yyyy-mm-dd ')  "statistics=none

B Library Test User:

Create ' +data ' off ; Create User  by default tablespace dbs_d_test;

Note: If on demand, not in the B library to establish the original table in a library corresponding table space, you need to set up a table in the B library, specify the table space B library, such as Dbs_d_test, and then imp import, otherwise you must first establish the previous table space.

Imp test/test file=t_jingyu_part.dmp log=imp_t_jingyu_part.log buffer=1024000 ignore=y full=y RESUMABLE=y

Exp/imp exporting the specified data from a production library table to a test library example