首页 » ORACLE » oracle imp/exp 从生产库导入测试库

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

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Claude Galgano | #1
    2011-12-21 at 04:13

    Purely to follow up on the update of this subject on your web site and would like to let you know how much I valued the time you took to produce this valuable post. Inside the post, you actually spoke of how to definitely handle this challenge with all ease. It would be my own pleasure to gather some more ideas from your web-site and come as much as offer other individuals what I have learned from you. Thanks for your usual excellent effort.