Oracle DB : Export only table data from from all tables of given schemas

Question

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 ?

• Michael Kutz: Is there any data in U2? Is there a physical difference between tables in U1 and U2?
• user236778: there is no physical difference between U1 and U2 . DB is installed on U2 with all schemas and blank tables already present there .

Answer 1

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.