• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2722
  • Last Modified:

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

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!
1 Solution
Use the ignore=y parameter when using IMP
imp user/pass file=file.dmp tables=<table_name> ignore=y
specify fromuser/touser if the table owned by other
why don't you use database link if you only need to copy data from one table to another?

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

chenshyAuthor Commented:
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!
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
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.
chenshyAuthor Commented:
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!
chenshyAuthor Commented:
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.
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 :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now