import utility in oracle

Question

i do export of an tablespace. This tablespace contains table1 with two rows say rowa and rowb/

now i delete rowb and insert new rowc into this tablespace.

Now i do import of this tablespace.

after importing i see the table1 in tablespace contains rowa, rowb,rowc but it was suppose to contain rowa and rowb.

Can anybody tell why is this happening so?

Answer 1

I presume you are using the old command line IMP utility, rather than Datapump?

IMP does not drop the existing table, nor does it clear out the existing data. If we set the flag IGNORE=Y then it will insert what data it can into the an extant table and report errors (duplicate keys) in the logfile.

Here is the current table ...

SQL> select * from t0;

COL1
----
rowA
rowC

SQL> 

... and here is what I exported ...

SQL> select * from t0;

COL1
----
rowA
rowB

SQL> 

An import with the default setting of IGNORE fails because the table exists but changing the flag succeeds:

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\temp>imp userid=APC tables=t0 file=apc.dmp

Import: Release 11.1.0.6.0 - Production on Thu Mar 25 18:22:21 2010

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

Password:

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

Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing APC's objects into APC
. importing APC's objects into APC
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T0" ("COL1" VARCHAR2(4) NOT NULL ENABLE)  PCTFREE 10 PCTUSED "
 "40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUP"
 "S 1 BUFFER_POOL DEFAULT)                       LOGGING NOCOMPRESS"
Import terminated successfully with warnings.

C:\temp>imp userid=APC tables=t0 file=apc.dmp ignore=y

Import: Release 11.1.0.6.0 - Production on Thu Mar 25 18:22:33 2010

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

Password:

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

Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing APC's objects into APC
. importing APC's objects into APC
. . importing table                           "T0"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (APC.SYS_C001014543) violated
Column 1 rowA          1 rows imported
Import terminated successfully with warnings.

C:\Documents and Settings\clarkean>exit

SQL> 

Note: one row was rejected (because COL1 is a primary key) and one rwo was inserted. So the table now has three rows:

SQL> select * from t0;

COL1
----
rowA
rowB
rowC

SQL>

If you an import/export process which does replace the data / data structures to the import then you should use Datapump instead. In fact you should use Datapump anyway, because it is by-and-large much better utility. Find out more.