Oracle 11g R2 – How to Export Dump of Schemas Excluding Some Tables

oracleoracle-11g-r2

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

However, exported whole schemas with MYTABLE1 and MYTABLE2.

Best Answer

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
******************************************************************************
Related Question