一、把11g数据导入到10g数据库中(expdp/impdp)
2.导出11g数据
3.在10g上创建目录并授权
4.查看用户hr下的表
5.将导出的文件上传到SERVER中相应的目录 ----工具
7.验证
可以看到,导入成功。
3.测试
4.导出文件
5.导入文件
1.在11g连接并创建目录和授权
C:\Users\JACK>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 11月 3 10:41:14 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory emp_dir as 'E:\app\exp';
目录已创建。
SQL> grant read ,write on directory emp_dir to public;
授权成功。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 11月 3 10:41:14 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory emp_dir as 'E:\app\exp';
目录已创建。
SQL> grant read ,write on directory emp_dir to public;
授权成功。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
2.进行导出
C:\Users\JAMES>expdp hr/hr directory=emp_dir dumpfile=emp.dump tables=emp_bak version=10.2.0.1.0
Export: Release 11.2.0.1.0 - Production on 星期六 11月 3 10:42:27 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "HR". "SYS_EXPORT_TABLE_01": hr/******** directory=emp_dir dumpfile=emp.dump tables=emp_bak ve
rsion=10.2.0.1.0
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "HR". "EMP_BAK" 16.11 KB 107 行
已成功加载/卸载了主表 "HR". "SYS_EXPORT_TABLE_01"
******************************************************************************
HR.SYS_EXPORT_TABLE_01 的转储文件集为:
E:\APP\EXP\EMP.DUMP
作业 "HR". "SYS_EXPORT_TABLE_01" 已于 10:42:42 成功完成
C:\Users\JAMES>expdp hr/hr directory=emp_dir dumpfile=emp.dump tables=emp_bak version=10.2.0.1.0
Export: Release 11.2.0.1.0 - Production on 星期六 11月 3 10:42:27 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "HR". "SYS_EXPORT_TABLE_01": hr/******** directory=emp_dir dumpfile=emp.dump tables=emp_bak ve
rsion=10.2.0.1.0
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "HR". "EMP_BAK" 16.11 KB 107 行
已成功加载/卸载了主表 "HR". "SYS_EXPORT_TABLE_01"
******************************************************************************
HR.SYS_EXPORT_TABLE_01 的转储文件集为:
E:\APP\EXP\EMP.DUMP
作业 "HR". "SYS_EXPORT_TABLE_01" 已于 10:42:42 成功完成
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 3 10:46:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory imp_dir as '/home/oracle';
Directory created.
SQL> grant read,write on directory imp_dir to public;
Grant succeeded.
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 3 10:46:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory imp_dir as '/home/oracle';
Directory created.
SQL> grant read,write on directory imp_dir to public;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
JOB_HISTORY
EMPLOYEES
7 rows selected.
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
JOB_HISTORY
EMPLOYEES
7 rows selected.
6.进行导入
[oracle@root ~]$ impdp hr/hr directory=imp_dir dumpfile=emp.dump tables=emp_bak version=10.2.0.1.0
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 10:48:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR". "SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR". "SYS_IMPORT_TABLE_01": hr/******** directory=imp_dir dumpfile=emp.dump tables=emp_bak version=10.2.0.1.0
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR". "EMP_BAK" 16.11 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR". "SYS_IMPORT_TABLE_01" successfully completed at 10:48:14
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 10:48:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "HR". "SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR". "SYS_IMPORT_TABLE_01": hr/******** directory=imp_dir dumpfile=emp.dump tables=emp_bak version=10.2.0.1.0
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR". "EMP_BAK" 16.11 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR". "SYS_IMPORT_TABLE_01" successfully completed at 10:48:14
SQL> conn hr/hr
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMP_BAK
COUNTRIES
JOB_HISTORY
EMPLOYEES
8 rows selected.
SQL> select employee_id,salary from emp_bak where rownum<11;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMP_BAK
COUNTRIES
JOB_HISTORY
EMPLOYEES
8 rows selected.
SQL> select employee_id,salary from emp_bak where rownum<11;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
二、使用客服端
1.删除先前导入的表emp_bak
2.配置tnsnames.ora文件
SQL> drop table emp_bak;
Table dropped.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
JOB_HISTORY
EMPLOYEES
7 rows selected.
Table dropped.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
JOB_HISTORY
EMPLOYEES
7 rows selected.
ORACLE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
[oracle@localhost admin]$ tnsping oracle
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-NOV-2012 10:52:42
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle)))
OK (10 msec)
[oracle@localhost admin]$ sqlplus hr/hr@oracle
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 3 10:53:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-NOV-2012 10:52:42
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle)))
OK (10 msec)
[oracle@localhost admin]$ sqlplus hr/hr@oracle
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 3 10:53:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
[oracle@root ~]$ exp hr/hr@oracle file=/home/oracle/emp.dmp tables=emp_bak
Export: Release 10.2.0.1.0 - Production on Sat Nov 3 10:57:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMP_BAK 107 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings. ----可以在导出命令中加入statistic=none避免此问题。
Export: Release 10.2.0.1.0 - Production on Sat Nov 3 10:57:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMP_BAK 107 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings. ----可以在导出命令中加入statistic=none避免此问题。
[oracle@localhost ~]$ imp hr/hr file=/home/oracle/emp.dmp tables=emp_bak
Import: Release 10.2.0.1.0 - Production on Sat Nov 3 10:58:38 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table "EMP_BAK" 107 rows imported
Import terminated successfully without warnings.
Import: Release 10.2.0.1.0 - Production on Sat Nov 3 10:58:38 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table "EMP_BAK" 107 rows imported
Import terminated successfully without warnings.
本文出自 “Focus on Oracle” 博客,请务必保留此出处http://alexy.blog.51cto.com/6115453/1085493