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 ?
2
  • Is there any data in U2? Is there a physical difference between tables in U1 and U2? – Michael Kutz Jun 20 '20 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 Jun 20 '20 at 11:03
0

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.

1
  • thanks for reply let me try this ! – user236778 Jun 20 '20 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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