mysql expdp gtid_EXPDP/IMPDP更改用户 表空间 表名

1、创建directory

JZH@test>create directory test_dir as '/home/oracle/';

Directory created.

2、创建测试表

JZH@test>create table emp1 as select * from scott.emp;

Table created.

3、查看表所在表空间

JZH@test>select owner,table_name,tablespace_name from dba_tables where table_name='EMP1';

OWNER                          TABLE_NAME                     TABLESPACE_NAME

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

JZH                            EMP1                           USERS

expdp jzh/jzh dumpfile=emp1.dmp directory=test_dir tables=emp1 logfile=emp1.log

Export: Release 11.2.0.3.0 - Production on Sat Sep 27 15:09:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "JZH"."SYS_EXPORT_TABLE_01":  jzh/******** dumpfile=emp1.dmp directory=test_dir tables=emp1 logfile=emp1.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "JZH"."EMP1"                                8.562 KB      14 rows

Master table "JZH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for JZH.SYS_EXPORT_TABLE_01 is:

/home/oracle/emp1.dmp

Job "JZH"."SYS_EXPORT_TABLE_01" successfully completed at 15:09:40

4、查询emp1表

select empno,ename,sal,comm from emp1;

EMPNO ENAME             SAL       COMM

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

7369 SMITH            1900

7499 ALLEN            1600        300

7521 WARD             1250        500

7566 JONES            2975

7654 MARTIN           1250       1400

7698 BLAKE            2850

7782 CLARK            2450

7788 SCOTT            3000

7839 KING             5000

7844 TURNER           1500          0

7876 ADAMS            1100

EMPNO ENAME             SAL       COMM

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

7900 JAMES             950

7902 FORD             3000

7934 MILLER           1300

14 rows selected.

Package body created.

5、更改表名，更改表空间，更改用户

impdp park/park dumpfile=emp1.dmp directory=test_dir logfile=emp1.log remap_schema=jzh:park remap_tablespace=users:example remap_table=emp1:emp2

6、检查结果

PARK@test>select owner,table_name,tablespace_name from dba_tables where table_name='EMP2';

OWNER                          TABLE_NAME                     TABLESPACE_NAME

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

PARK                           EMP2                           EXAMPLE

用户已由jzh更改为park,表名由EMP1更改为EMP2，表空间由USERS更改为EXAMPLE

6、查询数据

PARK@test>select empno,ename,sal,comm from emp2;

EMPNO ENAME             SAL       COMM

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

7369 SMITH            1900

7499 ALLEN            1600        300

7521 WARD             1250        500

7566 JONES            2975

7654 MARTIN           1250       1400

7698 BLAKE            2850

7782 CLARK            2450

7788 SCOTT            3000

7839 KING             5000

7844 TURNER           1500          0

7876 ADAMS            1100

EMPNO ENAME             SAL       COMM

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

7900 JAMES             950

7902 FORD             3000

7934 MILLER           1300

14 rows selected.

OK，完成！