Suppose I have 2 tables, TABLE_A
and TABLE_B
in production database, their primary keys are A_ID
and B_ID
respectively.
I want to export a row from TABLE_A
and TABLE_B
to a dump file using exp
command. The data I want are the result of following 2 queries.
TABLE_A
SELECT * FROM TABLE_A WHERE A_ID = 1001;
TABLE_B
SELECT * FROM TABLE_B WHERE B_ID = 9999;
I searched around and found only following syntax that is close but still not exactly what I want because TABLE_B
does not have column A_ID
exp userid=me/mypass@dbname tables=me.TABLE_A,me.TABLE_B query=\"WHERE A_ID=1001\"
I tried
exp userid=me/mypass@dbname tables=me.TABLE_A,me.TABLE_B query=me.TABLE_A:\"WHERE A_ID=1001\",me.TABLE_B:\"WHERE B_ID=9999\"
but it did not work, just got following error
LRM-00112: multiple values not allowed for parameter 'query'
Please help suggest how can I export a row from TABLE_A
and TABLE_B
in the same dump file.
(Moved the update to an answer according to @Alex suggestion)
If you're using the old export (exp) then no, you'd need to do a separate export for each table.
If you're using data pump (expdp) then yes, you can specify multiple QUERY clauses and specify which table each applies too.
Source: Multiple table export in oracle