CODEWITHSUNDEEP
oracle-databaseplsqldatapump
peete
peete

Reputation: 3

DBMS_DATAPUMP API export/import tables from different schemas

I would like to export import tables from multiple schemas with DBMS_DATAPUMP API. EG user1.table1 user2.table2 user3.table3 I give in a parameter the tables like a list with comma separated. 'user1.table1,user2.table2,user3.table3' After that I store in a table the list of tables. Then I read in a cursor the content of the table and go through in the cursor with a LOOP and give the schemas and table names one by one.

LOOP
 dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN('table1'));
 dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => 'IN('user1'));
END LOOP.

The first table is successfully added to the dbms_datapump job, but the second table exit with error.

ORA-39071: Value of SCHEMA_LIST is badly formed. ORA-00936: missing exprension

I tired to find solutions how to exp/imp with DBMS_DATAPUMP API tables from different schemas but I have found any examples. The examples whih I found only shows if you are exp/imp from one schema. Thanks in advance

Upvotes: 0

Views: 2841

Answers (2)

Erik
Erik

Reputation: 1

The syntax using a variable for the SCHEMA_EXPR is not valid. "'SCHEMA_EXPR', 'IN('||vschemas||')');"

Upvotes: 0

Дмитрий Решетило
Дмитрий Решетило

Reputation: 16

--For Table mode, only a single SCHEMA_EXPR filter is supported. If specified, it must only specify a single schema (for example, 'IN (''SCOTT'')').

DBMS_DATAPUMP.METADATA_FILTER(handle, 'SCHEMA_EXPR', 'IN('||vschemas||')');

--you can enter more than one table name, but no more than 4000 characters (literal limit), including special characters.

DBMS_DATAPUMP.METADATA_FILTER(handle, 'NAME_EXPR', 'IN('||vtables||')', 'TABLE');

Upvotes: 0

Related Questions