0

Scenario :

  • I want to export all data(or rows) from all tables from given schemas let say s1, s2, s3 from oracle DB installed on unix server(U1) .
  • This data has to imported on another oracle DB installed on another unix server(U2) .
  • On U2 all the tables are created already, just need to import data from U1 db .
  • I tried exporting for few schema using sql developer . But its a kind of manual thing and have around 20 schemas .
  • And I don't want to export other objects like views, synonymn, procedure etc only data from each table .

Question:

  • So, how this can be achieved using expdp, impdp command line utility ? or is there any better method ?
Improve this question
2
  • Is there any data in U2? Is there a physical difference between tables in U1 and U2?
    – Michael Kutz
    Commented Jun 20, 2020 at 10:58
  • there is no physical difference between U1 and U2 . DB is installed on U2 with all schemas and blank tables already present there .
    – user236778
    Commented Jun 20, 2020 at 11:03

1 Answer 1

Reset to default
2

Use Datapump (expdp/impdp):

expdp username/password directory=DATAPUMP_DIR schemas=S1,S2,S3 content=DATA_ONLY dumpfile=schema_dump.dmp logfile=export.log

impdp username/password directory=DATAPUMP_DIR dumpfile=schema_dump.dmp logfile=import.log

See here for an excellent reference: https://oracle-base.com/articles/10g/oracle-data-pump-10g

Note: If you export the metadata too (lose the "content=DATA_ONLY" option) then you can have the import process truncate your target tables before reloading them, by specifying the "TABLE_EXISTS_ACTION=TRUNCATE" option.

Improve this answer
1
  • thanks for reply let me try this !
    – user236778
    Commented Jun 20, 2020 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.