11g数据导入10g数据库方法实现

关注

原创最新推荐文章于 2026-03-20 00:49:31 发布 635 阅读 AI 写同款· GEO 优化 ›

ORACLE11G导入到10G的整个流程一、oracle的导入导出规则 1）IMP/EMP特性： Oracle的imp/exp组件的一个操作原则就是向下兼容，且有一些规则：规则1：低版本的exp/imp可以连接到高版本（或同版本）的数据库服务器，但高版本的exp/imp不能连接到低版本的数据库服务器。规则2：高版本exp出的dmp文件，低版本无法imp（无法识别dmp文件）；低版本exp出的dmp文件，高版本可以imp（向下兼容... 阅读详情

一、把11g数据导入到10g数据库中（expdp/impdp)

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 断开

2.导出11g数据

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 成功完成

3.在10g上创建目录并授权

[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.

4.查看用户hr下的表

SQL> conn hr/hr
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
COUNTRIES
JOB_HISTORY
EMPLOYEES
7 rows selected.

5.将导出的文件上传到SERVER中相应的目录 ----工具

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

7.验证

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.

可以看到，导入成功。

二、使用客服端

1.删除先前导入的表emp_bak

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.

2.配置tnsnames.ora文件

ORACLE =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.110)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = oracle)
        )
    )

3.测试

[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>

4.导出文件

[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避免此问题。

5.导入文件

[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.

本文出自 “Focus on Oracle” 博客，请务必保留此出处http://alexy.blog.51cto.com/6115453/1085493

收录于