oracle imp/exp 从生产库导入测试库

最常用到的就是从生产库导个方案出来，再导入到测试库，那当然是imp /exp 方便，虽然现在10g有了数据泵impdp/expdp,但对于开发人员把导出文件自己导入还是imp简单些
imp是insert 操作，所以不会覆盖里面已存在的数据
imp导入如果表存在加IGNORE   =   Y   参数只能是IMP出错时,进程将不会被中断，忽略错误,如果表存在，是追加
现在实验现在开始
打开个ms-dos
f:/exp zygl_js/zygl_sj@p212  file=zygl_js
生成zygl_js.dmp 文件
开始导入，导入到测试库一个叫jszygl的用户下，
为了数据的完整性可以考虑把原来的用户删除，但删除时如果发现这个用户已存在drop user就会提示 不能删除正在连接的用户，只好杀掉他的session,方法
SQL> select sid, SERIAL#,username from v$session where username='JSZYGL';
SID    SERIAL# USERNAME
---------- ---------- ------------------------------
2136      13968 JSZYGL
2148      18117 JSZYGL
2157      42964 JSZYGL
SQL> alter user jszygl account lock;   --锁掉用户防止期间再建连接
User altered.
SQL> alter system kill session '2136,13968';
System altered.
SQL> alter system kill session '2148,18117';
System altered.

SQL> alter system kill session '2157,42964';
System altered.
SQL> select sid, SERIAL#,username,status from v$session where username='JSZYGL';
SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
2136      13968 JSZYGL                         KILLED
2148      18117 JSZYGL                         KILLED
2157      42964 JSZYGL                         KILLED
2168      12965 JSZYGL                         KILLED
SQL> drop user jszygl cascade;
User dropped.
SQL> create user jszygl identified by jszygl;
User created.
SQL> grant connect,resource to jszygl;
Grant succeeded.
SQL> grant create view to jszygl;
Grant succeeded.
[oracle@develop ~]$ imp jszygl/jszygl file=zygl_js.DMP
Import: Release 10.2.0.4.0 - Production on Tue Dec 7 10:35:08 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZYGL_JS, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ZYGL_JS's objects into JSZYGL
Import terminated successfully without warnings.
--提示是那个导出的文件不是你这个用户导出的，如果在测试库新建的用户也叫zygl_js，用上面的可以么？你自己试试，可以！现在用dba 导入指定fromuser,touser
[oracle@develop ~]$ imp hysh/"xxxxxx" fromuser=zygl_js touser=jszygl file=zygl_js.DMP
Import: Release 10.2.0.4.0 - Production on Tue Dec 7 10:38:30 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZYGL_JS, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ZYGL_JS's objects into JSZYGL
. . importing table                    "AREA_CODE"      25269 rows imported
. . importing table               "BASE_EQUIPMENT"         79 rows imported
. . importing table              "BASE_MEDICAMENT"        600 rows imported
. . importing table        "BASE_MEDICAMENT_CLASS"          3 rows imported
. . importing table         "BASE_MEDICAMENT_USED"      12843 rows imported
. . importing table                     "DATA_DIC"       1328 rows imported
. . importing table                  "DATA_SOURCE"          1 rows imported
. . importing table             "DATA_SOURCE_ITEM"          2 rows imported
. . importing table                      "DISEASE"        692 rows imported
. . importing table             "DISEASE_DIAGNOSE"       2270 rows imported
. . importing table                     "EMPLOYEE"       2419 rows imported
成功
ps:如果导入的用户是个已锁定的导入会提示
IMP-00058: ORACLE error 28000 encountered
ORA-28000: the account is lockedUsername:
SQL> select username,account_status from dba_users;
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW                      OPEN
SYS                            OPEN
DBSNMP                         OPEN
SYSMAN                         OPEN
JSZYGL                         OPEN
SYSTEM                         LOCKED(TIMED)
SCOTT                          LOCKED(TIMED)
OUTLN                          EXPIRED & LOCKED