一 描述

将官档中imp实验一一转为自己的内容.
演示从用户scott,hr导出的exp文件,将hr.departments,hr.employees两张表导入到scott_test用户下.
导出基础环境参考文档:
EXP Example Export Session in User Mode

二 操作环境

OS info

windowsserver2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL> col value for a50
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.0.1.1.1

20 rows selected.

SQL>

other

脚本文件存储目录:C:\oracle\product\9.2.0\db_1\db_script\exp_imp

三 过程设计

3.1 imp参数文件内容imp_parameter_user.dat
userid=scott
FILE=scott.dmp
SHOW=n
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=hr
TOUSER=scott_test
TABLES=(departments,employees)

3.2 imp命令
> imp  PARFILE=imp_parameter_user.dat

3.3 imp日志信息

四 详细步骤操作

4.1 权限授予

C:\oracle\product\9.2.0\db_1\db_script\exp_imp>set nls_lang=american_america.zhs16gbk

C:\oracle\product\9.2.0\db_1\db_script\exp_imp>sqlplus "/ as sysdba

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Aug 23 09:42:52 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\oracle\product\9.2.0\db_1\db_script\exp_imp>

4.2 创建imp_parameter_user.dat文件(略)

4.3 执行imp操作

C:\oracle\product\9.2.0\db_1\db_script\exp_imp>imp parfile=imp_parameter_user.dat

Import: Release 9.0.1.1.1 - Production on Thu Aug 23 09:43:40 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

Export file created by EXPORT:V09.00.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing HR's objects into SCOTT_TEST
. . importing table                  "DEPARTMENTS"         27 rows imported
. . importing table                    "EMPLOYEES"        107 rows imported
IMP-00017: following statement failed with ORACLE error 942:
 "ALTER TABLE "DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATI"
 "ON_ID") REFERENCES "LOCATIONS" ("LOCATION_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
 "ALTER TABLE "EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") "
 "REFERENCES "JOBS" ("JOB_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00041: Warning: object created with compilation warnings
 "CREATE TRIGGER "SCOTT_TEST".secure_employees"
 "  BEFORE INSERT OR UPDATE OR DELETE ON employees"
 ""
 "BEGIN"
 "  secure_dml;"
 "END secure_employees;"
IMP-00041: Warning: object created with compilation warnings
 "CREATE TRIGGER "SCOTT_TEST".update_job_history"
 "  AFTER UPDATE OF job_id, department_id ON employees"
 "  FOR EACH ROW"
 ""
 "BEGIN"
 "  add_job_history(:old.employee_id, :old.hire_date, sysdate,"
 "                  :old.job_id, :old.department_id);"
 "END;"
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
 "ALTER TABLE "DEPARTMENTS" ENABLE CONSTRAINT "DEPT_LOC_FK""
IMP-00017: following statement failed with ORACLE error 2430:
 "ALTER TABLE "EMPLOYEES" ENABLE CONSTRAINT "EMP_JOB_FK""
Import terminated successfully with warnings.

C:\oracle\product\9.2.0\db_1\db_script\exp_imp>

(注:imp departments与employees两个张已经导入数据,但后续启用约束及其他类型定义时报相关表不存在错误,因为我们没有导入其他相关表,所以人为忽略此处错误吧.)

五 个人总结

从某个用户或某几个用户的导出文件中取某些表导入到其他用户,同样需要执行导入操作的用户具有imp_full_database的权限.注意导入参数文件的书写内容.

六 资料参考引用

Oracle9iDatabase Utilities
Release 1 (9.0.1)
Part Number A90192-01