expdp using dbms_datapump

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Tue, 27 Jan 2009 09:56:54 -0800
Message-ID: <a2b1e7610901270956q10c4e0ddj5cd0c8a69e3bcfdb_at_mail.gmail.com>

Hi List
Need help on how to write a subquery to filter only a subset of data. I got it to work using the following syntax at command line but I want to use dbms_datapump to acheive the same.

expdp system/xxxxxx DIRECTORY=DPDATA \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=k_schema \ INCLUDE=TABLE:\"IN \(SELECT table_name FROM dba_tables WHERE \ owner=\'K_SCHEMA\' AND LOGGING=\'NO\'\)\"

How to achieve the subquery portion using dbms_datapump? I have this but not sure if I can use subquery in this way and also I am having hard time parsing the single quotes for literals (K_SCHEMA)

declare
my_handle number;
begin
my_handle := dbms_datapump.open (operation => 'EXPORT',job_mode => 'SCHEMA', remote_link => NULL, job_name => 'my_job_name', version=>'LATEST' ) ;

  DBMS_DATAPUMP.add_file (handle         => my_handle,
                             filename       => 'xxcms.dmp',
                            DIRECTORY      => 'DATA_PUMP_DIR'
                           );

dbms_datapump.data_filter(HANDLE => my_handle,NAME => 'SUBQUERY', VALUE
=>'WHERE OWNER = ''K_SCHEMA'' AND LOGGING = ''NO''', TABLE_NAME =>

'DBA_TABLES');
dbms_datapump.start_job(my_handle);
dbms_output.put_line ('Started job ' || 'my_job_name' || ' with handle: ' ||

my_handle);
end;
/

Thank you
- Kumar

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 27 2009 - 11:56:54 CST