Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2880567
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2011-05-13 22:02:40


在11G之前,IMP的时候,如果表已经存在,那么导入会报错,此时如果我们要继续导入数据的话,需要设置IGNORE=Y。
而在11G中,IMP的时候,我们可以只导入数据。 看一个简单的例子:

[oracle@dbtest ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 13 21:55:07 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T SELECT ROWNUM,OBJECT_NAME FROM ALL_OBJECTS WHERE ROWNUM<=10;
 
10 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 ICOL$
         2 I_USER1
         3 CON$
         4 UNDO$
         5 C_COBJ#
         6 I_OBJ#
         7 PROXY_ROLE_DATA$
         8 I_IND1
         9 I_CDEF2
        10 I_OBJ5

10 rows selected.

SQL> host exp test/test file=/tmp/t.dmp tables=t

Export: Release 11.2.0.1.0 - Production on Fri May 13 21:58:28 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T         10 rows exported
Export terminated successfully without warnings.

SQL> truncate table t;

Table truncated.


如果不加IGNORE=Y ,那么导入会报错。

SQL> host imp test/test file=/tmp/t.dmp tables=t

Import: Release 11.2.0.1.0 - Production on Fri May 13 21:59:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST's objects into TEST
. importing TEST's objects into TEST
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T" ("ID" NUMBER(*,0), "NAME" VARCHAR2(30))  PCTFREE 10 PCTUSE"
 "D 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS "
 "1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                       "
 "  LOGGING NOCOMPRESS"
Import terminated successfully with warnings.


在11G之前,我们只能采用如下的方法进行导入。


SQL> host imp test/test file=/tmp/t.dmp tables=t ignore=y

Import: Release 11.2.0.1.0 - Production on Fri May 13 22:00:11 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table                            "T"         10 rows imported
Import terminated successfully without warnings.

SQL> select * from t;

        ID NAME
---------- ------------------------------
         1 ICOL$
         2 I_USER1
         3 CON$
         4 UNDO$
         5 C_COBJ#
         6 I_OBJ#
         7 PROXY_ROLE_DATA$
         8 I_IND1
         9 I_CDEF2
        10 I_OBJ5

10 rows selected.


11G之后我们还可以采用DATA_ONLY选项只导入数据。

SQL> host imp test/test file=/tmp/t.dmp tables=t data_only=y

Import: Release 11.2.0.1.0 - Production on Fri May 13 22:01:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST's objects into TEST
. . importing table                            "T"         10 rows imported
Import terminated successfully without warnings.

SQL> select * from t;

        ID NAME
---------- ------------------------------
         1 ICOL$
         2 I_USER1
         3 CON$
         4 UNDO$
         5 C_COBJ#
         6 I_OBJ#
         7 PROXY_ROLE_DATA$
         8 I_IND1
         9 I_CDEF2
        10 I_OBJ5

10 rows selected.


 

阅读(12692) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~