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?
kfeinerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
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

0
schwertnerCommented:
Additionallbe be aware that Data Pump works stable only after
Oracle 10g R2.
0
schwertnerCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

kfeinerAuthor Commented:
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?
0
SujithData ArchitectCommented:
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.
0
schwertnerCommented:
Data Pump can do this job.

Also, with the Oracle 10g import utility, you can use a network connection so that you can directly import either a single or multiple table(s) from one server to another. Here are the steps you need to perform in order to import a table or tables from one server to another. First, assume that you have two servers, Server A and Server B, and you want to import a table or tables such as "EMP" and "DEPT" from server "A" to "B."

1. Go to Server B and create a database link that will access the database in Server A.
Example:
SQL> CREATE DATABASE LINK mylink2a CONNECT TO scott IDENTIFIED BY password USING 'mylink2a';
Note that 'scott' is a user in the database in Server "A" and that its password is 'password'.

2. Then, perform the following import dump command.
Example:
# impdp scott2/password2 TABLES=emp,dept DIRECTORY=dpump1 NETWORK_LINK=mylink2a
Note that 'scott2' is a user in the database in Server B and that its password is 'password2'.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwertnerCommented:
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.
0
schwertnerCommented:
Dear AngelIII,
sorry for my mistake!
Seems I read only the first sentece... my mistake.
I am so sorry!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.