10g数据导入9i数据库方法实现

原创

Alexy_Young 2012-12-11 12:23:02 博主文章分类：oracle instance ©著作权

文章标签 oracle9i 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者Alexy_Young的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.配置本机的tnsnames.ora文件

[oracle@localhost admin]$ more tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
INST1_HTTP =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVER = SHARED)
            (SERVICE_NAME = MODOSE)
            (PRESENTATION = http://HRService)
        )
    )
ORCL =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orcl)
        )
    )

添加如下内容

[oracle@localhost admin]$ vi tnsnames.ora
ORCL10G =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCL10g)
        )
    )

2.测试连通性

[oracle@localhost ~]$ tnsping orcl10g
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 28-OCT-2012 03:28:53
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL10g)))
OK (10 msec)

3.查看本机hr用户模式的表

[oracle@localhost ~]$ sqlplus hr/hr
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 28 03:25:31 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
7 rows selected.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

4.导出数据

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ls
[oracle@localhost ~]$ exp hr/hr@orcl10g file=/home/oracle/emp_bak.dmp tables=emp_bak log=/home/oracle/emp_bak.log
Export: Release 9.2.0.4.0 - Production on Sun Oct 28 03:30:39 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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.

5.导入

[oracle@localhost ~]$ imp hr/hr file=/home/oracle/emp_bak.dmp tables=emp_bak log=/home/oracle/emp_bak1.log
Import: Release 9.2.0.4.0 - Production on Sun Oct 28 03:31:45 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. . importing table "EMP_BAK" 107 rows imported
Import terminated successfully without warnings.

6.验证

[oracle@localhost ~]$ sqlplus hr/hr
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 28 03:31:53 2012
Copyright (c) 1982, 2002, Oracle Corporation.    All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_BAK
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
8 rows selected.
SQL> select last_name,salary from emp_bak where rownum<9;
LAST_NAME                                         SALARY
------------------------- ----------
OConnell                                                2600
Grant                                                     2600
Whalen                                                    4400
Hartstein                                            13000
Fay                                                         6000
Mavris                                                    6500
Baer                                                     10000
Higgins                                                12008
8 rows selected.

可以指导，导入成功！