oracle imp/exp 导出、导入

转自http://www.anbob.com/?p=128
从生产库导入测试库
最常用到的就是从生产库导个方案出来，再导入到测试库，那当然是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


实际中使用到例:
导出sid_test_db库中表t_user到dmp文件,
EXP sa/123@SID_P_DB FILE=f:\expdb\db_20121030_1.DMP tables=(sa.t_user)

导入到SID_TEST库中
imp userid=usa/111@SID_TEST FILE=f:\expdb\db_20121030_1.DMP tables=(t_user)