How to use imp to import data to existing tables using the EXPDAT.DMP file?

Posted on 2003-11-06
Medium Priority
Last Modified: 2011-10-03
Dear All,

I used exp to export calldrb table on one database and expdat.dmp file is created. I copied the dmp file over to another server which has the same database setup as the one i did the export. I used imp to import the data to the database with existing calldrb table. The process failed because it prompted me the table has existed, aborting. Any idea how i can insert the data only  into the existing calldrb table using imp? The calldrb table which i want to insert data into has no data in it. Please help me. Thank you!
Question by:chenshy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 9692975
Use the ignore=y parameter when using IMP

Expert Comment

ID: 9693002
imp user/pass file=file.dmp tables=<table_name> ignore=y
specify fromuser/touser if the table owned by other

Expert Comment

ID: 9693840
why don't you use database link if you only need to copy data from one table to another?

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 9694742
Danielz,  how to use database link? i need to copy few millions records from one database server to another database server with same calldrb table fast!

And i got an example from the website.

exp scott/tiger tables=emp query=\"where deptno=10\"

I need to change the query to
query=\"where tran_id like '031003%'\"
but i got error something like bad ()...
Is the syntax wrong somewhere? Since the data is so huge, i may need to copy data day by day.

Can anyone please advise me? Thank you v v much!

Expert Comment

ID: 9694751
imp user/pass file=file.dmp tables=<table_name> ignore=y rows = Y indexes=N constarints=N - and if there arer some triggers=N-
specify fromuser/touser if the table owned by other

Expert Comment

ID: 9697300
Try this
imp user/password file=expdat.dmp ignore=y fromuser=server1user touser=server2user

Here is help on import  (8.1.7)

You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:

     Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword  Description (Default)       Keyword      Description (Default)
USERID   username/password           FULL         import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY  overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
ANALYZE  execute ANALYZE statements in dump file (Y)
FEEDBACK display progress every x rows(0)
TOID_NOVALIDATE  skip validation of specified type ids
FILESIZE maximum size of each dump file
RECALCULATE_STATISTICS recalculate statistics (N)

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set

Import terminated successfully without warnings.

Author Comment

ID: 9698269
tks everybody but i need to have the correct syntax to exp the data from calldrb table day by day.

i tried the following:
exp scott/tiger tables=emp query="where tran_id like '031004%'"

i got the error LRM-00101: unknown parameter name 'tran_id'
i desc calldrb, it contains column "tran_id".
can anyone help me to get the correct syntax? tks!

Author Comment

ID: 9698641
Dear all,

I manage to get the correct syntax and it runs perfectly on my test server.
The syntax is
exp emp/myemp tables=calldrb query=\" where tran_id \like \'031004%\' \"

However, when i used the same command to run at the live server which has the same database setup as the test server, i got the following errors

Connected to: Oracle8i Enterprise Edition Release - 64bit Production
With the Partitioning option
JServer Release - 64bit Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully

anybody knows what's wrong? tks in advance.

Accepted Solution

a_twixt_in_the_tale earned 160 total points
ID: 10957717
Update patch set to 8.7.4.x.x
ur test server mite have the patch applied

Then run catexp.sql, catproc.sql, prvtgdk.plb, prvtbfla.plb, utlgdk.sql logging in as system/oracle ( sum mite not run. its ok)
Found in ...ora81\RDBMS\ADMIN

Check with your dbadmin before u do this. i read sumwhere dat this screwed up a db. but its not happened to me.... yet :)


Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question