1

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.

2 Answers 2

2

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
******************************************************************************
1

The way SQL Developer's GUI constructs this PL/SQL block...you pick one or more tables, build up a list, and it spits out

dbms_datapump.metadata_filter(
  handle   => h1,
  name     => 'SCHEMA_EXPR',
  value    => 'IN(''HR'')'
);

dbms_datapump.metadata_filter(
  handle   => h1,
  name     => 'NAME_EXPR',
  value    => 'IN(''ABSTRACTS'',''CUSTOMERS'',''IDENTITY_EXAMPLE'',''UNTAPPD'')'
);

You can find the Data Pump Export wizard on the View > DBA page.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.