Re: Import utility limitation

Thanks for your replies,

Right now i understand that exp/imp isn't good way to backup and restore the database, may be i have to try another alternative.

In our application software, we allow user to restore their data when he/she think need to do it.

I am really new in oracle but not in sqlserver, this news group helps me so much to solve my problems in oracle. Thanks.

Regards,

Rudy Susanto

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0205240433.45be9aa1_at_posting.google.com>...
> 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 Sun May 26 2002 - 20:49:52 CDT