环境：平台分别是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行。
则导入表成功。
二、导入用户模式
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
导入用户模式成功。
三、导入表空间
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行。
导入表空间成功。
四、导入全库
在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行。
导入成功。
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |