expdp迁移分区表，impdp导入合并分区表

expdp迁移分区表，impdp导入合并分区表

1 创建目录对象，主备库都要同样创建

[oracle@sp11 ~]$ sqlplus / as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create directory dir1 as '/home/oracle';

Directory created.

2 主库导出

 expdp TABLES=hr.part_tab USERID="' / as sysdba'" DIRECTORY=dir1 DUMPFILE=part_tab.dmp LOGFILE=part_tab.log query=\"where amt\<410\";

3 备库导入

impdp USERID="'/ as sysdba'" TABLES=hrpart_tab DIRECTORY=dir1 DUMPFILE=part_tab.dmp LOGFILE=imp_part_tab.log REMAP_SCHEMA=hr:scott PARTITION_OPTIONS=merge;

expdp TABLES=scott.sales USERID="' / as sysdba'" DIRECTORY=dir1 DUMPFILE=sales.dmp LOGFILE=sales.log query=\"where cust_id\<400\";

测试引用分区表的导出导入

创建主表：

SQL> create table customers 

  2  (cust_id   number primary key,

  3  cust_name varchar2(200),

  4   rating    varchar2(1) not null )

  5  partition by list (rating) 

  6  (

  7  partition pA values ('A'),

  8  partition pB values ('B')

  9  );

Table created.

创建子表

SQL> create table sales

  2  (

  3  sales_id    number primary key, 

  4  cust_id     number not null,

  5  sales_amt   number,

  6  constraint  fk_sales_01

  7  foreign key (cust_id) references customers)

  8  partition by reference (fk_sales_01);

Table created.

主表和子表数据分布

SQL> select * from customers;

   CUST_ID CUST_NAME                      RA

---------- ------------------------------ --

       100 c1                             A

       300 c3                             A

       200 c2                             B

       400 c4                             B

SQL> select * from sales;

  SALES_ID    CUST_ID  SALES_AMT

---------- ---------- ----------

         1        100       1000

         2        300       2000

         3        200       3000

         4        400       4000

查询主表和子表的分区表类型

SQL> l

  1* select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME from   dba_tab_partitions where  TABLE_NAME = 'SALES' and TABLE_OWNER = 'SCOTT'

TABLE_OWNER          TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------

SCOTT                SALES                PA                   USERS

SCOTT                SALES                PB                   USERS

SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME from   dba_tab_partitions where  TABLE_NAME = 'CUSTOMERS' and TABLE_OWNER = 'SCOTT'  ;

TABLE_OWNER          TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------

SCOTT                CUSTOMERS            PA                   USERS

SCOTT                CUSTOMERS            PB                   USERS

导出操作：

导出主表

expdp TABLES=scott.customers USERID="' / as sysdba'" DIRECTORY=dir1 DUMPFILE=customers.dmp LOGFILE=customers.log query=\"where cust_id\<400\";

导出子表

expdp TABLES=scott.sales USERID="' / as sysdba'" DIRECTORY=dir1 DUMPFILE=sales.dmp LOGFILE=sales.log query=\"where cust_id\<400\";

expdp TABLES=scott.sales USERID="' / as sysdba'" DIRECTORY=dir1 DUMPFILE=sales.dmp exclude=constraint LOGFILE=sales.log query=\"where cust_id\<400\"; <<<去掉约束

导入操作：

导入主表

impdp USERID="'/ as sysdba'" TABLES=scott.customers DIRECTORY=dir1 DUMPFILE=customers.dmp LOGFILE=imp_sales1.log REMAP_SCHEMA=scott:hr PARTITION_OPTIONS=merge;

导入子表

impdp USERID="'/ as sysdba'" TABLES=scott.sales DIRECTORY=dir1 DUMPFILE=sales.dmp LOGFILE=imp_sales.log REMAP_SCHEMA=scott:hr PARTITION_OPTIONS=merge;