Re: Import utility limitation

rsusanto_at_atlas-sp.com (Rudy Susanto) wrote in message news:<1a928d0b.0205231911.bf020d7_at_posting.google.com>...
> Hi all,
>
> Are there any limitations on import utility? I can use this tool to
> import new records into the tables but i can't replace the existing
> records from the exported file. Can we use this utility to restore the
> data?
>
> Here the example for the details:
>
> SQL> conn scott/tiger
> Connected.
> SQL> select empno, ename from emp;
> EMPNO ENAME
> ---------- ----------
> 7369 SMITH
> 7499 ALLEN
> 7521 WARD
> 7566 JONES
> 7654 MARTIN
> 7698 BLAKE
> 7782 CLARK
> 7788 SCOTT
> 7839 KING
> 7844 TURNER
> 7876 ADAMS
> 7900 JAMES
> 7902 FORD
> 7934 MILLER
> 14 rows selected.
>
> And then i export the tables,
> D:\Oracle\Ora81\BIN>exp scott/tiger file=c:\scott.dmp
> Done successfully...
>
> After that i update the record on table emp,
> SQL>update emp set ename='JACK' where empno=7900;
> 1 row updated.
> SQL> commit;
> Commit complete.
>
> From DOS-prompt, i import from the exported file,
> D:\Oracle\Ora81\BIN>imp scott/tiger file=c:\scott.dmp tables=emp
> ignore=y
>
>
> And then i check the result, but still the same.
> SQL> select empno, ename from emp;
> EMPNO ENAME
> ---------- ----------
> 7369 SMITH
> 7499 ALLEN
> 7521 WARD
> 7566 JONES
> 7654 MARTIN
> 7698 BLAKE
> 7782 CLARK
> 7788 SCOTT
> 7839 KING
> 7844 TURNER
> 7876 ADAMS
> 7900 JACK
> 7902 FORD
> 7934 MILLER
> 14 rows selected.
>
> Should i delete all records on the table before importing data? Is
> there any better ways to do this job? I know a little about rman but i
> still don't know how to use it.
>
>
> Thanks in advance,
>
> Rudy Susanto

Not a limitation, but a feature. The dump is a logical backup. It will just issue INSERTS for all records in the dump. If there are PKs on the target table, the INSERT simply will fail with an error. ignore=y will allow imp to proceed, so this is why your record didn't change.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Fri May 24 2002 - 06:24:47 CDT