CODEWITHSUNDEEP
oracle-databaseoracle-export-dump
asinkxcoswt
asinkxcoswt

Reputation: 2544

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)

Upvotes: 0

Views: 11890

Answers (3)

Nandha kumar
Nandha kumar

Reputation: 763

Try this

expdp system/xxxxxx tables=TABLE_A query=\"WHERE A_ID = 1001\"

Upvotes: 0

asinkxcoswt
asinkxcoswt

Reputation: 2544

Thanks to @mehmet suggestion, I gave up the straight solution and started finding a work-around solution. (I cannot use expdp because I don't have access to the database server.

I create a table EXP_ROW_ID as follows

CREATE TABLE EXP_ROW_ID (ROW_ID VARCHAR(20));

Then I put the ROWID of the rows I want to export from the 2 tables

INSERT INTO EXP_ROW_ID SELECT ROWID FROM TABLE_A WHERE A_ID = 1001;
INSERT INTO EXP_ROW_ID SELECT ROWID FROM TABLE_B WHERE B_ID = 9999;

Then I export the dump using following command

exp userid=me/mypass@dbname tables=me.TABLE_A,me.TABLE_B query=\"WHERE ROWID IN (SELECT T.ROW_ID FROM EXP_ROW_ID T)\"

Upvotes: 2

mehmet sahin
mehmet sahin

Reputation: 812

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

Upvotes: 1

Related Questions