Skip to Main Content
  • Questions

  • datapump export using DBMS_DATAPUMP package

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 23, 2011 - 3:34 am UTC

Last updated: December 20, 2016 - 12:50 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

I have to export many tables on different schemas in a single dump file, using DBMS_DATAPUMP.

If I run this command the export goes fine:

expdp fr/fr dumpfile=prova.dmp logfile=prova.log directory=dfr tables=MOD_BASE.PROBE_PROFILE,MOD_DNS.SCENARIO


But with DBMS_DATAPUMP I receive the following error: ora-39166 object was not found into the log file.

The pl/sql code is the following, wath's wrong?

l_filename := p_filename||'_'|| to_char(SYSDATE,'yyyy-mm-dd_hh24:mi:ss');
l_job_name := 'EOD_' || to_char(SYSDATE,'yyyymmddhh24miss');
l_dp_handle :=
      DBMS_DATAPUMP.OPEN (operation   => 'EXPORT',
                          job_mode    => 'TABLE',
                          remote_link => NULL,
                          job_name    => l_job_name,
                          VERSION     => 'LATEST');
       DBMS_DATAPUMP.add_file (handle      => l_dp_handle,
                  filename    => l_filename||'.dmp',
                  DIRECTORY   => 'DFR');
       DBMS_DATAPUMP.add_file (handle      => l_dp_handle,
                  filename    => l_filename || '.log',
                  DIRECTORY   => 'DFR',
                  filetype    => DBMS_DATAPUMP.ku$_file_type_log_file);
        DBMS_DATAPUMP.metadata_filter (handle      => l_dp_handle,
                         NAME        => 'NAME_LIST',
                         VALUE       => '''MOD_BASE.PROBE_PROFILE'',''MOD_DNS.SCENARIO''',
                        object_type => 'TABLE');

        DBMS_DATAPUMP.set_parameter (handle      => l_dp_handle,
                         NAME        => 'INCLUDE_METADATA',
                         VALUE       => 0);

       DBMS_DATAPUMP.start_job (l_dp_handle);

       DBMS_DATAPUMP.detach (l_dp_handle);

and Tom said...

here is the approach, you do a schema level export - but you only include the tables of interest:

ops$tkyte%ORA11GR2> create or replace directory datapump_dir as '/tmp'
  2  /

Directory created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec utl_file.fremove( 'DATAPUMP_DIR', 'mydp.dmp' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec utl_file.fremove( 'DATAPUMP_DIR', 'mydp.log' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_datapump_handle    NUMBER;
  3      l_datapump_dir       VARCHAR2(20) := 'DATAPUMP_DIR';
  4      l_status             varchar2(200);
  5  
  6  BEGIN
  7      l_datapump_handle := dbms_datapump.open(operation => 'EXPORT' ,
  8                                              job_mode =>'SCHEMA' ,
  9                                              job_name => 'MY JOB' );
 10  
 11      dbms_datapump.add_file(handle    => l_datapump_handle ,
 12                             filename  => 'mydp.dmp' ,
 13                             directory => l_datapump_dir);
 14  
 15      dbms_datapump.add_file(handle    => l_datapump_handle ,
 16                             filename  => 'mydp.log' ,
 17                             directory => l_datapump_dir ,
 18                             filetype  => DBMS_DATAPUMP.ku$_file_type_log_file);
 19  
 20      dbms_datapump.metadata_filter( handle => l_datapump_handle,
 21                                     name   => 'SCHEMA_LIST',
 22                                     value  => q'|'OPS$TKYTE','SCOTT'|' );
 23  
 24      dbms_datapump.metadata_filter( handle => l_datapump_handle,
 25                                     name   => 'NAME_EXPR',
 26                                     value  =>  q'|in ('EMP','T')|',
 27                                     object_path=> 'TABLE' );
 28  
 29      dbms_datapump.start_job(handle => l_datapump_handle);
 30      dbms_datapump.wait_for_job( handle => l_datapump_handle,
 31                                  job_state => l_status );
 32      dbms_output.put_line( l_status );
 33  END;
 34  /
COMPLETED

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> !ls -ltr /tmp/mydp.*
-rw-rw-r-- 1 ora11gr2 ora11gr2   1037 Dec 23 17:36 /tmp/mydp.log
-rw-rw---- 1 ora11gr2 ora11gr2 192512 Dec 23 17:36 /tmp/mydp.dmp

ops$tkyte%ORA11GR2> !cat /tmp/mydp.log
Starting "OPS$TKYTE"."MY JOB":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "OPS$TKYTE"."T"                                 0 KB       0 rows
Master table "OPS$TKYTE"."MY JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.MY JOB is:
  /tmp/mydp.dmp
Job "OPS$TKYTE"."MY JOB" successfully completed at 17:36:19

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Datapump with errors

Rajeshwaran Jeyabal, December 25, 2011 - 12:18 am UTC

Tom:

I was practicing your example, in my local database and ended up with below errors. Can you help me what i am missing here?

rajesh@ORA11GR2>
rajesh@ORA11GR2> declare
  2     l_handle number;
  3     l_status Varchar2(200);
  4  begin
  5     l_handle := dbms_datapump.open(operation=>'EXPORT',
  6                             job_mode=>'SCHEMA',
  7                             job_name=>'MY_JOB');
  8
  9     dbms_datapump.add_file(handle=>l_handle,
 10             filename=>'data1.dmp',
 11             directory=>'TKDIR');
 12
 13     dbms_datapump.add_file(handle=>l_handle,
 14             filename=>'log1.txt',
 15             directory=>'TKDIR',
 16             filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
 17
 18     dbms_datapump.metadata_filter(handle=>l_handle,
 19             name=>'SCHEMA_LIST',
 20             value=>q'|'SCOTT','HR'|');
 21
 22      dbms_datapump.metadata_filter(handle=>l_handle,
 23             name=>'NAME_LIST',
 24             value=>q'|in ('EMP','EMPLOYEES') |',
 25             object_path=>'TABLE');
 26
 27     dbms_datapump.start_job(handle=>l_handle);
 28     dbms_datapump.wait_for_job(handle=>l_handle,
 29             job_state=>l_status);
 30
 31     dbms_output.put_line ('status = '||l_status);
 32
 33  exception
 34     when others then
 35             dbms_output.put_line ( dbms_utility.format_error_backtrace );
 36             dbms_datapump.stop_job(l_handle);
 37             raise_application_error (-20458,sqlerrm);
 38  end;
 39  /
status = COMPLETED

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.44
rajesh@ORA11GR2>

and the log file shows this.
Starting "RAJESH"."MY_JOB":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
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
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39166: Object in ('EMP' was not found.
ORA-39166: Object 'EMPLOYEES') was not found.
Master table "RAJESH"."MY_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJESH.MY_JOB is:
  C:\ORACLE\10.2.0\DIAG\RDBMS\ORA11GR2\ORA11GR2\TRACE\DATA1.DMP
Job "RAJESH"."MY_JOB" completed with 2 error(s) at 00:09:44

And i have SELECT privilege on both tables.
rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from dba_tab_privs
  3  where (owner, table_name) in (('SCOTT','EMP'),('HR','EMPLOYEES'))
  4  and grantee = user;

GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE  GRANTABLE  HIERARCHY
---------- ---------- ---------- ---------- ---------- ---------- ----------
RAJESH     HR         EMPLOYEES  HR         SELECT     NO         NO
RAJESH     SCOTT      EMP        SCOTT      SELECT     NO         NO

Elapsed: 00:00:00.03
rajesh@ORA11GR2>



Tom Kyte
December 25, 2011 - 10:59 am UTC 

ops$tkyte%ORA11GR2> create or replace directory TKDIR as '/tmp'
  2  /

Directory created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2     l_handle number;
  3     l_status Varchar2(200);
  4  begin
  5     l_handle := dbms_datapump.open(operation=>'EXPORT',
  6                             job_mode=>'SCHEMA',
  7                             job_name=>'MY_JOB');
  8  
  9     dbms_datapump.add_file(handle=>l_handle,
 10             filename=>'data1.dmp',
 11             directory=>'TKDIR');
 12  
 13     dbms_datapump.add_file(handle=>l_handle,
 14             filename=>'log1.txt',
 15             directory=>'TKDIR',
 16             filetype=>dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
 17  
 18     dbms_datapump.metadata_filter(handle=>l_handle,
 19             name=>'SCHEMA_LIST',
 20             value=>q'|'SCOTT','HR'|');
 21  
 22      dbms_datapump.metadata_filter(handle=>l_handle,
 23             name=>'NAME_EXPR',
 24             value=>q'|in ('EMP','EMPLOYEES') |',
 25             object_path=>'TABLE');
 26  
 27     dbms_datapump.start_job(handle=>l_handle);
 28     dbms_datapump.wait_for_job(handle=>l_handle,
 29             job_state=>l_status);
 30  
 31     dbms_output.put_line ('status = '||l_status);
 32  
 33  exception
 34     when others then
 35             dbms_output.put_line ( dbms_utility.format_error_backtrace );
 36             dbms_datapump.stop_job(l_handle);
 37             raise_application_error (-20458,sqlerrm);
 38  end;
 39  /
status = COMPLETED

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> !cat /tmp/log1.txt
Starting "OPS$TKYTE"."MY_JOB":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
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
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES"                            16.81 KB     107 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "OPS$TKYTE"."MY_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.MY_JOB is:
  /tmp/data1.dmp
Job "OPS$TKYTE"."MY_JOB" successfully completed at 11:57:32



see line 23, you materially changed my example. A list would not be "sql", I used a name_expr

Datapump with errors

Rajeshwaran Jeyabal, December 25, 2011 - 11:43 am UTC

Thanks Tom, I changed the line23 and Job got completed sucessfully.
   dbms_datapump.metadata_filter(handle=>l_handle,
    name=>'NAME_EXPR',
    value=>q'|in ('EMP','EMPLOYEES') |',
    object_path=>'TABLE');

I was referring to this link to find the difference between NAME_LIST and NAME_EXPR. But i don't get it clearly
From this i dont understand the difference between NAME_EXPR and NAME_LIST. Both parameters are used to Identify the objects to be picked up for processing.

1) Can you explain the difference between NAME_LIST and NAME_EXPR ?
2) A list would not be "sql" - Can you explain on this with reference to above Context? I didnot get you clearly

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_datpmp.htm#BABJACDG

Tom Kyte
December 25, 2011 - 2:02 pm UTC


use this link instead of the one right above...
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_datpmp.htm#ARPLS66044


name_list is a simple list - 'a','b','c'

name_expr is a SQL expression that will be used with a query to generate a list. name_expr could be

like 'X%'

for example, or as I coded:

in ('A','B','C')


Rupesh, May 20, 2014 - 10:51 am UTC

Hi Tom,

I tried this approach but getting an error, please advise.

Error starting at line 12 in command:
DECLARE
dp_handle NUMBER;
l_status Varchar2(200);
BEGIN
dp_handle := dbms_datapump.open(
operation => 'EXPORT',
job_mode => 'SCHMEA',
job_name => 'TEST_EXPORT_6',
version => 'LATEST');

dbms_datapump.add_file(
handle => dp_handle,
filename => 'RUPTEST.dmp',
DIRECTORY => 'test_dir',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

dbms_datapump.add_file(
handle => dp_handle,
filename => 'RUPTEST.log',
DIRECTORY => 'test_dir',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

dbms_datapump.metadata_filter(handle=>dp_handle,
NAME=>'SCHEMA_LIST',
value=>q'|'SYSMAN'|');

dbms_datapump.metadata_filter(handle=>dp_handle,
NAME=>'NAME_LIST',
value=>q'|in ('JAM_LINEHASH_NORM','JAM_JVM') |',
object_path=>'TABLE');

dbms_datapump.start_job(handle=>dp_handle);
dbms_datapump.wait_for_job(handle=>dp_handle,
job_state=>l_status);
dbms_output.put_line ('status = '||l_status);

exception
when others then
dbms_output.put_line ( dbms_utility.format_error_backtrace );
dbms_datapump.stop_job(dp_handle);
raise_application_error (-20458,sqlerrm);
END;
Error report:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5658
ORA-06512: at line 40
31623. 00000 - "a job is not attached to this session via the specified handle"
*Cause: An attempt to reference a job using a handle which is invalid or
no longer valid for the current session.
*Action: Select a handle corresponding to a valid active job or start a
new job.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5296
ORA-06512: at line 5



Rupesh, May 20, 2014 - 12:01 pm UTC

Hi,

Please ignore my previous post. I've corrected the issue. Many thanks for such a nice example.

response

javier, July 04, 2014 - 8:59 am UTC

Hi,

can you tell us the solution for this errors?

Thks a lot.

JaviD.

Filtering rows

Alexandre, September 15, 2016 - 2:47 pm UTC

Hi Tom,

Is it possible to combine the metadata_filter of specific tables with data_filters so some tables come empty?

Like, I want the tables ('EMP','EMPLOYEES'), but i need to export the Employee table with no records, just the structure of the table.

Tried to mix the commands but with no luck.

Thanks
Chris Saxon
December 20, 2016 - 12:50 am UTC

C:\Users\comcdona>cat c:\temp\x.par
directory=TEMP
dumpfile=scott.dmp
logfile=scott.log
query=DEPT:"WHERE 1=0"
query=EMP:"WHERE 1=0"

C:\Users\comcdona>expdp scott/tiger parfile=c:\temp\x.par

Export: Release 12.1.0.2.0 - Production on Tue Dec 20 08:48:50 2016

Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** parfile=c:\temp\x.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."ABC" 5.898 KB 1 rows
. . exported "SCOTT"."DEPT" 5.937 KB 0 rows
. . exported "SCOTT"."EMP" 8.210 KB 0 rows
. . exported "SCOTT"."SALGRADE" 5.960 KB 5 rows
. . exported "SCOTT"."T" 5.054 KB 1 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 20 08:49:59 2016 elapsed 0 00:01:04
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.