一 描述
将官档中imp实验一一转为自己的内容.演示从用户scott,hr导出的exp文件,将hr.departments,hr.employees两张表导入到scott_test用户下.
导出基础环境参考文档:
EXP Example Export Session in User Mode
二 操作环境
OS info
windows
server2003 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.datuserid=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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-741676/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-741676/