Home » RDBMS Server » Server Utilities » dbms_datapump - data_filter not working (Oracle 10.2.0.4.0 on RHEL Linux)
dbms_datapump - data_filter not working [message #376661] Thu, 18 December 2008 03:37
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

I am trying to import object from one schema into another schema in another database using database link using following code

However while using data_filter with 'SUBQUERY' I am getting 'ORA-39001: invalid argument value' error

Also using data_filer with 'INCLUDE_ROWS' with any value 0 Or 1 does not import any rows but if I do not include the 'INCLUDE_ROWS' rows are imported.

Please suggest.

Thanks and Regards,
Pratap


importing database objects from expid schema in one db server into impid schema in another server using db link conn_expid

connect expid/expid

create table exptab(n number);

insert into exptab values(1);

insert into exptab values(9);

create table exptab1(n number);

insert into exptab1 values(1);

insert into exptab1 values(7);

create table exptab2(n number);

insert into exptab2 values(2);

insert into exptab2 values(8);

commit;

************
another db server

conn impid/impid


DECLARE
handle0 number;
Begin
handle0 := DBMS_DATAPUMP.open(
        operation => 'IMPORT',
        job_mode  => 'TABLE',
        remote_link => 'CONN_EXPID',
        job_name    => 'JOB31',
        version => 'LATEST'
        );


DBMS_DATAPUMP.METADATA_REMAP (
handle=>handle0,
name=>   'REMAP_SCHEMA',
old_value=>   'EXPID',
   value=>   'IMPID');


DBMS_DATAPUMP.METADATA_FILTER(
   handle=>handle0,
   name=>'NAME_EXPR',
   value=>'IN (''EXPTAB'',''EXPTAB1'')');

--not working
DBMS_DATAPUMP.METADATA_FILTER(
   handle=>handle0,
   name=>'SCHEMA_EXPR',
   value=>'IN (''EXPID'')');
/*
DBMS_DATAPUMP.DATA_FILTER(
   handle=>handle0,
   name=>'INCLUDE_ROWS',
   value=>1);
*/

[COLOR=red]DBMS_DATAPUMP.DATA_FILTER(
   handle=>handle0,
   name=>'SUBQUERY',
   value=>'WHERE N=1',
   table_name=>'EXPTAB'
   );[/COLOR]

--not working
DBMS_DATAPUMP.add_file(
    handle    => handle0,
    filename  => 'check31.log',
    directory => 'IMPDIR',
    filetype=>3);

DBMS_DATAPUMP.SET_PARAMETER(handle0,'TABLE_EXISTS_ACTION','SKIP');

    DBMS_DATAPUMP.start_job(handle0);
    DBMS_DATAPUMP.detach(handle0);

end;
/

[Updated on: Thu, 18 December 2008 03:39] by Moderator

Report message to a moderator

Previous Topic: How to use SQLLoader to load multiple tables from multile files.
Next Topic: SQL Loader and control file changes for different users
Goto Forum:
  


Current Time: Mon Dec 05 15:05:41 CST 2016

Total time taken to generate the page: 0.10713 seconds