How to export dump of some schemas exclude some tables I need to find the way make an quick and light weight dump export. It is the reason why I want to export only specific scheme and to exclude large tables.

I use Enterprise Manager for it. Data Movement > Move Row Data > Export to Export Files > Schemas > add need schemas > Show Advanced Options > Exclude Only Objects Specified Below > Add Another Row > select table and type need table names > next..next..next

EM generate next PL\SQL 

declare
  h1 NUMBER;
begin
  h1 := dbms_datapump.open(operation =&gt;'EXPORT', declare h1 NUMBER;
  begin
    h1 := dbms_datapump.open(operation =&gt; 'EXPORT',
                             job_mode  =&gt; 'SCHEMA',
                             job_name  =&gt; 'FAST_TRY_6',
                             version   =&gt; 'COMPATIBLE');
    dbms_datapump.set_parallel(handle =&gt; h1, degree =&gt; 1);
    dbms_datapump.add_file(handle    =&gt; h1,
                           filename  =&gt; 'efast.LOG',
                           directory =&gt; 'backup',
                           filetype  =&gt; 3);
    dbms_datapump.set_parameter(handle =&gt; h1,
                                name   =&gt; 'KEEP_MASTER',
                                value  =&gt; 0);
    dbms_datapump.metadata_filter(handle =&gt; h1,
                                  name   =&gt; 'SCHEMA_EXPR',
                                  value  =&gt; 'IN(''MYSCHEMA1'',''MYSCHEMA2'')');
    dbms_datapump.add_file(handle    =&gt; h1,
                           filename  =&gt; 'efast181015.DMP',
                           directory =&gt; 'backup',
                           filetype  =&gt; 1);
    dbms_datapump.set_parameter(handle =&gt; h1,
                                name   =&gt; 'INCLUDE_METADATA',
                                value  =&gt; 1);
    dbms_datapump.set_parameter(handle =&gt; h1,
                                name   =&gt; 'DATA_ACCESS_METHOD',
                                value  =&gt; 'AUTOMATIC');
    dbms_datapump.metadata_filter(handle      =&gt; h1,
                                  name        =&gt; 'EXCLUDE_NAME_EXPR',
                                  value       =&gt; 'IN (''"MYSCHEMA1"."MYTABLE1"'',''"MYSCHEMA2"."MYTABLE2"'')',
                                  object_type =&gt; 'TABLE');
    dbms_datapump.set_parameter(handle =&gt; h1,
                                name   =&gt; 'ESTIMATE',
                                value  =&gt; 'BLOCKS');
    dbms_datapump.start_job(handle       =&gt; h1,
                            skip_current =&gt; 0,
                            abort_step   =&gt; 0);
    dbms_datapump.detach(handle =&gt; h1);
  end;
  /


However, exported whole schemas with MYTABLE1 and MYTABLE2.
Afaik, that syntax never worked like that, I am surprised EM generated this (well, not really...)

grant dba to bp1 identified by bp1;
grant dba to bp2 identified by bp2;
create table bp1.t1 as select * from dba_users;
create table bp1.t2 as select * from dba_users;
create table bp2.t1 as select * from dba_users;
create table bp2.t2 as select * from dba_users;

$ cat exp1.par
schemas=BP1,BP2
exclude=table:" in ('"BP1"."T1"', '"BP2"."T2"')"


This does not work as expected, because the database compares the given strings to table_name and that does not include the owner:

$ expdp \'/ as sysdba\' parfile=exp1.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  19.07 KB      27 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  19.07 KB      27 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************


If you do this:

$ cat exp2.par
schemas=BP1,BP2
exclude=table:" in ('T1', 'T2')"


This excludes the tables, but from all schemas:

$ expdp \'/ as sysdba\' parfile=exp2.par
...
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************


What you can do is, specify the QUERY parameter with a condition that evaluates to false for the tables to be skipped. This will still export the table, but without any data (0 rows).

$ cat exp3.par
schemas=BP1,BP2
query=BP1.T1:"WHERE 1=0"
query=BP2.T2:"WHERE 1=0"

$ expdp \'/ as sysdba\' parfile=exp3.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  14.99 KB       0 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  14.99 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************