FAQ
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

Search Discussions

  • Nigel Thomas at Jan 28, 2009 at 8:34 am
    Kumar

    I think you are really trying to filter metadata (what tables to export)
    rather than data (what data to export from a particular table).

    Here is a similar example (from a recent OTN forum
    post<http://forums.oracle.com/forums/thread.jspa?forumID=61&threadID=846857>,
    where the formatting should be clearer):

    declare

    handle number;

    state varchar2(20);

    begin

    handle := dbms_datapump.open

    (operation=>'EXPORT',job_mode=>'TABLE'

    );

    dbms_datapump.add_file(

    handle => handle,

    filename => 'test.dmp',

    directory => 'TEST_DIR'

    );

    dbms_datapump.metadata_filter

    (handle=>handle,name=>'NAME_EXPR',value=>'IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE
    ''P%'')'

    );

    dbms_datapump.start_job(handle=>handle);

    dbms_datapump.wait_for_job(handle=>handle, job_state=>state);

    dbms_output.put_line('State='||state);

    end;

    /
    Regards Nigel

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 27, '09 at 5:56p
activeJan 28, '09 at 8:34a
posts2
users2
websiteoracle.com

2 users in discussion

Nigel Thomas: 1 post Kumar Madduri: 1 post

People

Translate

site design / logo © 2022 Grokbase