Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1894
  • Last Modified:

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?
0
kfeiner
Asked:
kfeiner
  • 6
1 Solution
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now