Situatie

Aveți două baze de date (una locală și una la distanță) și intenționați să reîmprospătați unele tabele locale cu date din tabele la distanță. Pentru a demonstra acest lucru este creat următorul mediu:

connect / as sysdba

— test user
create user test identified by <password> default tablespace users temporary tablespace temp;
grant connect, resource to test;

— facem obiecte in schema TEST
connect test/parola

— test tables
create table a_tab
(
id number,
text varchar2(50)
);

create table b_tab
(
id number,
text varchar2(50)
);

— populare tabele de test
insert into a_tab values (1, ‘Remote schema, table A_TAB – Row 1’);
insert into a_tab values (2, ‘Remote schema, table A_TAB – Row 2’);
insert into b_tab values (1, ‘Remote schema, table B_TAB – Row 1’);
insert into b_tab values (2, ‘Remote schema, table B_TAB – Row 2’);
commit; 

 

create table transfer_objects
(
owner varchar2(30),
table_name varchar2(30),
object_type varchar2(30)
);

—  tabele ce for fi actualizate in  baza locala 
insert into transfer_objects values (‘TEST’, ‘A_TAB’, ‘TABLE’);
insert into transfer_objects values (‘TEST’, ‘B_TAB’, ‘TABLE’);
commit;

 

Baza de date locala (target)

 

connect / as sysdba

— directorul folosit pentru logurile DataPump 
create directory refresh as ‘<PATH>’;

— test user
create user test identified by <password> default tablespace users temporary tablespace temp;
grant connect, resource, create database link to test;
grant read, write on directory refresh to test;

— creare obiecte in schema TEST
connect test/parola

–Creare database link din local in remote
create database link link_to_remote connect to test identified by <password> using ‘<TNS alias to remote>’;

create table a_tab
(
id number,
text varchar2(50)
);

create table b_tab
(
id number,
text varchar2(50)
);

— populare tabele de tes
insert into a_tab values (1, ‘Local schema, table A_TAB – Row 1’);
insert into b_tab values (1, ‘Local schema, table B_TAB – Row 1’);
commit;

Porniți următorul bloc PL/SQL care apelează API-ul DataPump în baza de date locală pentru a reîmprospăta tabelele A_TAB/B_TAB de la distanță:

set serverout on size 1000000
declare
my_handle number; — Data Pump job handle
my_db_link varchar2(30) := ‘LINK_TO_REMOTE’;
ind NUMBER; — Loop index
percent_done NUMBER; — Percentage of job complete
job_state VARCHAR2(30); — To keep track of job state
le ku$_LogEntry; — For WIP and error messages
js ku$_JobStatus; — The job status from get_status
jd ku$_JobDesc; — The job description from get_status
sts ku$_Status; — The status object returned by get_status

begin
— creare job 
my_handle := dbms_datapump.open
(
operation => ‘IMPORT’,
job_mode => ‘TABLE’,
remote_link => my_db_link
);

dbms_output.put_line (‘Opened DataPump job with handle: ‘||to_char (my_handle));dbms_datapump.set_parallel (my_handle, 1);
dbms_output.put_line (‘Added parallel’);

dbms_datapump.set_parameter
(
handle => my_handle,
name => ‘TABLE_EXISTS_ACTION’,
value => ‘REPLACE’
);
dbms_output.put_line (‘Added parameter REPLACE’);

dbms_datapump.add_file
(
handle => my_handle,
filename => ‘impdp_test.log’,
directory => ‘REFRESH’,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
dbms_output.put_line (‘Added log file’);

dbms_datapump.metadata_filter
(
handle => my_handle,
name => ‘NAME_EXPR’,
value => ‘IN (SELECT TABLE_NAME FROM TEST.TRANSFER_OBJECTS WHERE OWNER = ”TEST” AND OBJECT_TYPE = ”TABLE”)’,
object_type => ‘TABLE’
);
dbms_output.put_line (‘Added metadata filter’);

dbms_datapump.start_job (my_handle);

percent_done := 0;
job_state := ‘UNDEFINED’;
while (job_state != ‘COMPLETED’) and (job_state != ‘STOPPED’) loop
dbms_datapump.get_status
(
my_handle,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, -1 , job_state, sts
);
js := sts.job_status;

if js.percent_done != percent_done then
dbms_output.put_line (‘*** Job percent done = ‘||to_char (js.percent_done));
percent_done := js.percent_done;
end if;

if (bitand (sts.mask, dbms_datapump.ku$_status_wip) != 0) then
le := sts.wip;
else
if (bitand (sts.mask, dbms_datapump.ku$_status_job_error) != 0) then
le := sts.error;
else
le := null;
end if;
end if;

if le is not null then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line (le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;

dbms_output.put_line (‘Job has completed’);
dbms_output.put_line (‘Final job state = ‘||job_state);
dbms_datapump.detach (my_handle);
end;
/

Outputul ar trebui sa fie ca in exemplul de mai jos:

Opened DataPump job with handle: 18
Added parallel
Added parameter REPLACE
Added log file
Added metadata filter
Starting “TEST”.”SYS_IMPORT_TABLE_01″:
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
. . imported “TEST”.”A_TAB” 2 rows
. . imported “TEST”.”B_TAB” 2 rows
*** Job percent done = 100
Job “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:22:54
Job has completed
Final job state = COMPLETED

PL/SQL procedure successfully completed.

Conținutul tabelelor locale după importul DataPump este următorul:

SQL> select * from a_tab;

ID TEXT
————— ————————————————–
1 Remote schema, table A_TAB – Row 1
2 Remote schema, table A_TAB – Row 2

SQL> select * from b_tab;

ID TEXT
————— ————————————————–
1 Remote schema, table B_TAB – Row 1
2 Remote schema, table B_TAB – Row 2

Solutie

Tip solutie

Permanent