Solved

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

Posted on 2003-11-06
11
2,111 Views
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!
0
Comment
Question by:chenshy
11 Comments
 
LVL 2

Expert Comment

by:racher
ID: 9692975
Use the ignore=y parameter when using IMP
Graham
0
 
LVL 8

Expert Comment

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

Expert Comment

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

0
 

Author Comment

by:chenshy
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!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:redtiger
ID: 9694751
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
 
LVL 4

Expert Comment

by:Vinay_dba
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)
     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
 

Author Comment

by:chenshy
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!
0
 

Author Comment

by:chenshy
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 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
 
LVL 8

Accepted Solution

by:
a_twixt_in_the_tale earned 40 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 :)

:)
Don
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now