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

When you import into an existing table and there are unique indexes on the table then duplicate rows will be rejected. If there are no unique indexes then the data is inserted potentially creating a duplicate row situation depending on the business rules for the data.

If you want to replace the existing data with the backup either delete the existing rows or truncate the table. If you want to overlay only certain rows then you can do a fromuser touser imp into a copy of the table and use sql to extract the rows of interest.

See the Oracle Utilities manual for details on import, imp, export, exp, and sql loader, sqlldr.

HTH -- Mark D Powell -- Received on Fri May 24 2002 - 07:33:47 CDT