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!
chenshyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

racherCommented:
Use the ignore=y parameter when using IMP
Graham
0
baonguyen1Commented:
imp user/pass file=file.dmp tables=<table_name> ignore=y
specify fromuser/touser if the table owned by other
0
DanielztCommented:
why don't you use database link if you only need to copy data from one table to another?

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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!
0
redtigerCommented:
Hi
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
0
Vinay_dbaCommented:
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)
     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
               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.
0
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!
0
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 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 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.
0
a_twixt_in_the_taleCommented:
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 :)

:)
Don
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.