oracle根据生产库建立测试库,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

「喜欢文章，快来给作者赞赏墨值吧」 赞赏

【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源(墨天轮)，文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

请登录后发表评论