EXP/IMP: An Example of exporting the specified data from the production database table to the Test Database

EXP/IMP: An Example of exporting the specified data from the production database table to the Test Database

In general, EXP/IMP is the export and import program of the previous generation, and EXPDP/IMPDP is the next-generation export and import program. For export and import of large data volumes, EXPDP/IMPDP is the preferred choice. The degree of parallelism can be used, and the table space and other operations are more flexible. For migration of small data volumes, you can use exp/imp to simplify the operation.

Requirement: export part of t_jingyu_part data in the Partition Table of zjy database A (11.2.0.3), dbs_d_jingyu in the data table space, and dbs_ I _jingyu in the index tablespace.

Import imp to database B (11.2.0.4). The default tablespace dbs_d_test of user test is used.

Preparations for database A zjy users:

Create tablespace dbs_d_jingyu datafile '+ data' size 100 M autoextend off;
Create tablespace dbs_ I _jingyu datafile '+ data' size 20 M autoextend off;
Create user zjy identified by zjy default tablespace dbs_d_jingyu;

 

Create table t_jingyu_part (
Id number,
Deal_date date,
Area_code number,
Contents varchar2 (4000 ))
Partition by range (deal_date)
(Partition p1 values less than (to_date ('1970-02-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p2 values less than (to_date ('1970-03-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p3 values less than (to_date ('1970-04-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p4 values less than (to_date ('1970-05-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p5 values less than (to_date ('1970-06-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p6 values less than (to_date ('2017-07-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p7 values less than (to_date ('1970-08-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p8 values less than (to_date ('2017-09-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p9 values less than (to_date ('2017-10-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p10 values less than (to_date ('1970-11-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p11 values less than (to_date ('2017-12-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p12 values less than (to_date ('1970-01-01 ', 'yyyy-MM-DD') tablespace dbs_d_jingyu,
Partition p_max values less than (maxvalue) tablespace dbs_d_jingyu );

 

Insert into t_jingyu_part (id, deal_date, area_code, contents)
Select rownum, to_date (to_char (sysdate-365, 'J') + trunc (dbms_random.value (0,365), 'J'), ceil (dbms_random.value (590,599 )), rpad ('* ", 400,' * ') from dual
Connect by rownum <= 100000;
Commit;

Create index idx_t_jingyu_part_id 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 (1) from t_jingyu_part where deal_date> = to_date ('2017-11-11 ', 'yyyy-mm-dd') and deal_date <= to_date ('2017-12-12 ', 'yyyy-mm-dd ');

Exp zjy/zjy parfile = exp. par

File = t_jingyu_part.dmp
Log = exp_t_jingyu_part.log
Tables = t_jingyu_part
Query = "where deal_date> = to_date ('1970-11-11 ', 'yyyy-mm-dd') and deal_date <= to_date ('1970-12-12 ', 'yyyy-mm-dd ')"
Statistics = none

Direct path export is recommended when direct path export is available: direct = y, but this example does not apply.

Database B test Users:

Create tablespace dbs_d_test datafile '+ data' size 100 M autoextend off;
Create user test identified by test default tablespace dbs_d_test;

Note: If the table space corresponding to the source table created in database A is not in database B as required, you must first create A table in database B and specify the table space in database B, such as dbs_d_test; then import the data using imp. Otherwise, you must create a tablespace.

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

---------------------------- Lili split line ----------------------------

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031

Oracle 10gr2 rac expdp error UDE-00008 ORA-31626

Use of expdp/impdp to back up databases in Oracle

Oracle backup recovery (expdp/impdp)
 
-------------------------- Related parameters of Expdp/Impdp ----------------------------