Solved

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

Posted on 2003-11-06
11
2,259 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
[X]
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
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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
 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

726 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