oracle-databaseoracle-export-dump

Oracle exp, export with multiple queries


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.


Update

(Moved the update to an answer according to @Alex suggestion)


Solution

  • 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