expdp/impdp小练习

monkeybird

环境：平台分别是REDHAT 5.5和WINDOWS SP3，ORACLE数据库都是11gr2

一、导入表
1.在Linux机器上创建目录并授权
SQL> create directory expdp_dir as '/home/oracle';

Directory created.

SQL> grant read,write on directory expdp_dir to public;

Grant succeeded.

2.导出Linux用户Scott下的emp表
[oracle@dbserver ~]$ expdp scott/tiger directory=expdp_dir dumpfile=table.dmp logfile=table.log tables=emp;

Export: Release 11.2.0.1.0 - Production on Tue Oct 23 11:26:57 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=expdp_dir dumpfile=table.dmp logfile=table.log tables=emp
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
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/table.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:27:22

3.在windows机器上创建目录并授权
SQL> create directory impdp_dir as 'f:\impdp';

目录已创建。

SQL> grant read,write on directory impdp_dir to public;

授权成功。

4.将Linux中导出的文件拷贝到在windows里创建的目录中

5.新建用户aaa并授予相应权限
SQL> conn / as sysdba
已连接。
SQL> create user aaa identified by aaa;

用户已创建。

SQL> grant connect,resource to aaa;

授权成功。

6.将表emp导入到用户aaa下
C:\Documents and Settings\Administrator>impdp 'sys/oracle as sysdba' directory=impdp_dir dumpfile=table.dmp logfile=table_log tables=scott.emp remap_schema=scott:aaa

Import: Release 11.2.0.3.0 - Production on 星期三 10月 24 14:25:13 2012

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"
启动 "SYS"."SYS_IMPORT_TABLE_01":  "sys/******** AS SYSDBA" directory=impdp_dir
dumpfile=table.dmp tables=scott.emp remap_schema=scott:aaa
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "AAA"."EMP"                                 8.570 KB      14 行
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: 对象类型 REF_CONSTRAINT 创建失败, 出现错误:
ORA-00942: 表或视图不存在
失败的 sql 为:
ALTER TABLE "AAA"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERE
NCES "AAA"."DEPT" ("DEPTNO") ENABLE

处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "SYS"."SYS_IMPORT_TABLE_01" 已经完成, 但是有 1 个错误 (于 14:25:28 完成)
（注：这里出现的错误是正常的）

7.进行验证
SQL> conn aaa/aaa
已连接。
SQL> select table_name from tabs;

TABLE_NAME
-----------
EMP

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

则导入表成功。

monkeybird

二、导入用户模式
1.在Linux机器上创建目录并授权
SQL> create directory expdp_dir as '/home/oracle';

Directory created.

SQL> grant read,write on directory expdp_dir to public;

Grant succeeded.

2.导出Linux用户Scott
[oracle@dbserver ~]$ expdp scott/tiger directory=expdp_dir dumpfile=schemas.dmp logfile=schemas.log schemas=scott ;     

Export: Release 11.2.0.1.0 - Production on Wed Oct 24 14:32:45 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=expdp_dir dumpfile=schemas.dmp logfile=schemas.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/schemas.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:33:51

3.在windows机器上创建目录并授权
SQL> create directory impdp_dir as 'f:\impdp';

目录已创建。

SQL> grant read,write on directory impdp_dir to public;

授权成功。

4.将Linux中导出的文件拷贝到在windows里创建的目录中

5.新建用户aaa并授予相应权限
SQL> conn / as sysdba
已连接。
SQL> create user aaa identified by aaa;

用户已创建。

SQL> grant connect,resource to aaa;

授权成功。

6.将模式scott导入到用户模式aaa下
C:\Documents and Settings\Administrator>impdp 'sys/oracle as sysdba' directory=impdp_dir dumpfile=schemas.dmp logfile=schemas.log remap_schema=scott:aaa

Import: Release 11.2.0.3.0 - Production on 星期三 10月 24 14:38:37 2012

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_FULL_01"
启动 "SYS"."SYS_IMPORT_FULL_01":  "sys/******** AS SYSDBA" directory=impdp_dir d
umpfile=schemas.dmp remap_schema=scott:aaa
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . 导入了 "AAA"."DEPT"                                5.937 KB       4 行
. . 导入了 "AAA"."EMP"                                 8.570 KB      14 行
. . 导入了 "AAA"."SALGRADE"                            5.867 KB       5 行
. . 导入了 "AAA"."BONUS"                                   0 KB       0 行
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "SYS"."SYS_IMPORT_FULL_01" 已于 14:38:51 成功完成

7.进行验证
SQL> conn aaa/aaa
已连接。
SQL> select table_name from tabs;

TABLE_NAME
-----------
DEPT
EMP
BONUS
SALGRADE

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

导入用户模式成功。

monkeybird

三、导入表空间
1.在Linux机器上创建目录并授权
SQL> create directory expdp_dir as '/home/oracle';

Directory created.

SQL> grant read,write on directory expdp_dir to public;

Grant succeeded.

2.创建测试表空间
SQL> create tablespace tabs1 datafile '/oracle/app/oracle/oradata/sztech1/tabs101.dbf' size 10m;

Tablespace created.

SQL> create tablespace tabs2 datafile '/oracle/app/oracle/oradata/sztech1/tabs201.dbf' size 10m;

Tablespace created.

SQL> create user tab1 identified by tab1 default tablespace tabs1;

User created.

SQL> create user tab2 identified by tab2 default tablespace tabs2;

User created.

SQL> grant connect,resource to tab1,tab2;

Grant succeeded.

SQL> grant select on scott.emp to tab1,tab2;

Grant succeeded.

SQL> conn tab1/tab1
Connected.
SQL> create table tab1(id number);

Table created.

SQL> insert into tab1 select empno from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> conn tab2/tab2
Connected.
SQL> create table tab2 as select ename from scott.emp;

Table created.

3.导出表空间tabs1，tabs2
[oracle@dbserver ~]$ expdp \'/ as sysdba\' directory=expdp_dir dumpfile=tablesapce.dmp logfile=tablespace.log tablespaces=tabs1,tabs2;

Export: Release 11.2.0.1.0 - Production on Wed Oct 24 15:23:47 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=expdp_dir dumpfile=tablesapce.dmp logfile=tablespace.log tablespaces=tabs1,tabs2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TAB1"."TAB1"                               5.109 KB      14 rows
. . exported "TAB2"."TAB2"                               5.140 KB      14 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /home/oracle/tablesapce.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 15:24:06

4.在windows机器上创建目录并授权
SQL> create directory impdp_dir as 'f:\impdp';

目录已创建。

SQL> grant read,write on directory impdp_dir to public;

授权成功。

5.将Linux中导出的文件拷贝到在windows里创建的目录中

6.创建相应表空间和用户
SQL> create tablespace tabs1 datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\tabs101.dbf' size 10m;

表空间已创建。

SQL> create tablespace tabs2 datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\tabs201.dbf' size 10m;

表空间已创建。

SQL> create user tab1 identified by tab1 default tablespace tabs1;

用户已创建。

SQL> create user tab2 identified by tab2 default tablespace tabs2;

用户已创建。

SQL> grant connect,resource to tab1,tab2;

授权成功。

7.进行导入
C:\Documents and Settings\Administrator>impdp 'sys/oracle as sysdba' directory=impdp_dir dumpfile=tablespace.dmp tablespaces=tabs1,tabs2

Import: Release 11.2.0.3.0 - Production on 星期三 10月 24 15:39:50 2012

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLESPACE_01"
启动 "SYS"."SYS_IMPORT_TABLESPACE_01":  "sys/******** AS SYSDBA" directory=impdp
_dir dumpfile=tablespace.dmp tablespaces=tabs1,tabs2
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "TAB1"."TAB1"                               5.109 KB      14 行
. . 导入了 "TAB2"."TAB2"                               5.140 KB      14 行
作业 "SYS"."SYS_IMPORT_TABLESPACE_01" 已于 15:39:58 成功完成

8.进行验证
SQL> conn tab1/tab1
已连接。

SQL> select * from tab1;

        ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

已选择14行。

SQL> conn tab2/tab2
已连接。
SQL> select * from tab2;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

已选择14行。

导入表空间成功。

monkeybird

四、导入全库
在linux中做一些修改，方便验证导入成功

1.在Linux机器上创建目录并授权
SQL> create directory expdp_dir as '/home/oracle';

Directory created.

SQL> grant read,write on directory expdp_dir to public;

Grant succeeded.

2.创建测试表空间
SQL> create tablespace tabs1 datafile '/oracle/app/oracle/oradata/sztech1/tabs101.dbf' size 10m;

Tablespace created.

SQL> create tablespace tabs2 datafile '/oracle/app/oracle/oradata/sztech1/tabs102.dbf' size 10m;

Tablespace created.

SQL> create user tab1 identified by tab1 default tablespace tabs1;

User created.

SQL> create user tab2 identified by tab2 default tablespace tabs2;

User created.

SQL> grant connect,resource to tab1,tab2;

Grant succeeded.

SQL> grant select on scott.emp to tab1,tab2;

Grant succeeded.

SQL> conn tab1/tab1
Connected.
SQL> create table tab1(id number);

Table created.

SQL> insert into tab1 select empno from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> conn tab2/tab2
Connected.
SQL> create table tab2 as select ename from scott.emp;

Table created.

3.在scott下创建一张表并插入数据
SQL> conn scott/tiger
Connected.
SQL> create table employees as select * from emp;

Table created.

4.进行数据库的导出
[oracle@dbserver ~]$ expdp \'/ as sysdba\' directory=expdp_dir dumpfile=full.dmp logfile=full.log  full=y

Export: Release 11.2.0.1.0 - Production on Wed Oct 24 16:32:06 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_02":  "/******** AS SYSDBA" directory=expdp_dir dumpfile=full.dmp logfile=full.log full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 139.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
...............
...............
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_02 is:
  /home/oracle/full.dmp
Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at 16:46:20

5.在windows机器上创建目录并授权
SQL> create directory impdp_dir as 'f:\impdp';

目录已创建。

SQL> grant read,write on directory impdp_dir to public;

授权成功。

6.将Linux中导出的文件拷贝到在windows里创建的目录中

7.创建相应表空间并查看用户scott下的表
SQL> create tablespace tabs1 datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\tabs101.dbf' size 10m;

表空间已创建。

SQL> create tablespace tabs2 datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\tabs201.dbf' size 10m;

表空间已创建。

SQL> conn scott/tiger
已连接。
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

8.进行全库导入
C:\Documents and Settings\Administrator>impdp 'sys/oracle as sysdba' directory=impdp_dir dumpfile=full.dmp full=y

Import: Release 11.2.0.3.0 - Production on 星期三 10月 24 17:09:16 2012

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
................
................
作业 "SYS"."SYS_IMPORT_FULL_01" 已经完成, 但是有 8641 个错误 (于 14:25:28 完成)

9.进行验证
SQL> conn tab1/tab1
已连接。
SQL> select * from tab1;

        ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

        ID
----------
      7900
      7902
      7934

已选择14行。

SQL> conn tab2/tab2
已连接。
SQL> select * from tab2;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

已选择14行。

SQL> conn scott/tiger
已连接。
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMPLOYEES

SQL> select * from employees;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

导入成功。