Oraclue

Oracle internals, debugging and undocumented features

Export / Import data over db link – using dbms_datapump and dbms_file_transfer packages

— Datapump FULL MODE EXPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’EXPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED’;
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;

— Transfer dump file to the destination directory
dbms_file_transfer.put_file(
source_directory_object => ‘”DPUMP_DIR”‘,
source_file_name => ‘test.dmp’,
destination_directory_object => ‘”DPUMP_DIR”‘,
destination_file_name => ‘test.dmp’,
destination_database => ‘test.world’);

COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

PROMPT Connecting as  administrator to site 2
CONNECT username/password
— Datapump FULL MODE IMPORT

DECLARE
h1                NUMBER;       — data pump job handle
job_state         VARCHAR2(30); — job state
status            ku$_Status; — data pump status
job_not_exist     exception;
pragma            exception_init(job_not_exist, -31626);
BEGIN

h1 := dbms_datapump.open(operation=>’IMPORT’,job_mode=>’FULL’,
remote_link=>”,
job_name=>NULL, version=>’COMPATIBLE’);

dbms_datapump.set_parameter(h1, ‘STREAMS_CONFIGURATION’, 0);

dbms_datapump.add_file(
handle=>h1,
filename=>’test.dmp’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle=>h1,
filename=>’test.log’,
directory=>’DPUMP_DIR’,
filetype=>dbms_datapump.ku$_file_type_log_file);

dbms_datapump.start_job(h1);

job_state := ‘UNDEFINED’;
BEGIN
WHILE (job_state != ‘COMPLETED’) AND (job_state != ‘STOPPED’) LOOP
status := dbms_datapump.get_status(
handle => h1,
mask => dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,
timeout => -1);
job_state := status.job_status.state;
dbms_lock.sleep(10);
END LOOP;
EXCEPTION WHEN job_not_exist THEN
dbms_output.put_line(‘job finished’);
END;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: