asked on

Oracle 10 Datapump: how can I use the SYS.dbms_datapump package to import one table

I want to use the SYS.dbms_datapump package to import a SINGLE table from another database. I know that there is a client that can be called to initiate the datapump, but I dont want to call the client.... I want to directly call the SYS.dbms_datapump package.

Does anyone know how I can do this? Which specific procedure do I need to call and how do I call it?

schwertner

Yes it is possible.
See below the easiest example.
i use the API in very complicated environment with many
parameters formed as procedure executed by SYS

declare
    handle  number;
begin
    handle := dbms_datapump.open('EXPORT','SCHEMA');
    dbms_datapump.add_file(handle,'SCOTT3.DMP','DUMPDIR');
    dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','= ''SCOTT''');
    dbms_datapump.set_parallel(handle,4);
    dbms_datapump.start_job(handle);
    dbms_datapump.detach(handle);
end;
/

Open in new window

schwertner

Additionallbe be aware that Data Pump works stable only after
Oracle 10g R2.

schwertner

Example

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_dp_handle       NUMBER;
  l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  l_job_state       VARCHAR2(30) := 'UNDEFINED';
  l_sts             KU$_STATUS;
BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'EMP_EXPORT',
    version     => 'LATEST');
 
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.dmp',
    directory => 'TEST_DIR');
DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''SCOTT''');
 
  DBMS_DATAPUMP.start_job(l_dp_handle);
 
  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
Once the job has started the status can be checked using:
system@db10g> select * from dba_datapump_jobs;

Open in new window

kfeiner

ASKER

Let me target into what my need is. I do not wish to import or export into a file. i want to use the datapump package to grab a table from a database link and move that data into a table in my database.

Is this possible and if so, how do I call the datapump package for this?

Sujith

I dont understand how the databse link is relevant in this context.

Smaller tables can be pulled directly over databse links.
Larger tables has to be exported and imported, links doesnt come to picture here.

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

Pardon me!
The asker got exactly what he/she want and didn't post objections
or indicate that the solution doesn't work.
There is no reason to classify the given answers as
irrelevant or useles.

schwertner

Dear AngelIII,
sorry for my mistake!
Seems I read only the first sentece... my mistake.
I am so sorry!