LightDB24.1新特性Oracle模式支持dbms_datapump包导出数据

Oracle的DBMS_DATAPUMP是一个强大的工具，用于高效地将数据从一个数据库导出到另一个数据库，或者在同一个数据库中移动数据。它是Oracle提供的一种数据泵技术，用于实现数据迁移、备份和恢复等操作

LightDB24.1开始支持该特性，测试如下：

create database testdb01 with owner = lightdb encoding = 'UTF8' lightdb_syntax_compatible_type = 'oracle';
alter user lightdb with password 'lightdb';

lightdb@postgres=# \c testdb01 
You are now connected to database "testdb01" as user "lightdb".
compatible type: oracle

create schema at01;
grant usage on schema at01 to public;
create table at01.q1(id int,vals varchar2(32));
insert into at01.q1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table at01.w1(id int,vals varchar2(32));
insert into at01.w1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table at01.e1(id int,vals varchar2(32));
insert into at01.e1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table at01.r1(id int,vals varchar2(32));
insert into at01.r1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create schema bt01;
grant usage on schema bt01 to public;
create table bt01.a1(id int,vals varchar2(32));
insert into bt01.a1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table bt01.s1(id int,vals varchar2(32));
insert into bt01.s1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table bt01.d1(id int,vals varchar2(32));
insert into bt01.d1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

create table bt01.f1(id int,vals varchar2(32));
insert into bt01.f1(id,vals) select generate_series, '#' || generate_series from generate_series(1, 10000000);

select count(*) from at01.q1;
select count(*) from at01.w1;
select count(*) from at01.e1;
select count(*) from at01.r1;

select count(*) from bt01.a1;
select count(*) from bt01.s1;
select count(*) from bt01.d1;
select count(*) from bt01.f1;

create or replace directory dmpdir as '/home/lightdb/datapump';

select * from utl_file.utl_file_dir;

select dbms_output.serveroutput('t');

declare
    hdl numeric;
    jbs varchar2;
begin
    dbms_output.put_line('step 001 ' || systimestamp);
    hdl := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
    dbms_output.put_line('handle = ' || hdl);
    dbms_output.put_line('step 002 ' || systimestamp);
    dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
    dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => dbms_datapump.ku$_file_type_log_file);
    dbms_output.put_line('step 003 ' || systimestamp);
    dbms_datapump.metadata_filter(handle => hdl, name => 'SCHEMA_EXPR', value => 'LIKE ''%bt%''');
dbms_datapump.metadata_filter(handle => hdl, name => 'NAME_EXPR', value => 'LIKE ''%1%''');
dbms_datapump.set_parameter(handle => hdl, name => 'COMPRESSION', value => 'ALL');
    dbms_datapump.set_parallel(handle => hdl, degree => 2);
    dbms_output.put_line('step 004 ' || systimestamp);
    dbms_datapump.start_job(handle => hdl);
    dbms_output.put_line('step 005 ' || systimestamp);
    dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
    dbms_output.put_line('step 006 ' || systimestamp);
    dbms_datapump.stop_job(handle => hdl);
    dbms_output.put_line('step 007 ' || systimestamp);
end;
/

ERROR:  job already exists
HINT:  select a different job name, or stop the currently executing job and re-try the operation
CONTEXT:  PL/oraSQL function dbms_datapump.open(varchar2,varchar2,varchar2,varchar2,varchar2) line 28 at RAISE
PL/oraSQL function inline_code_block line 7 at assignment
复制

停止该EXAMPLE001 job

lightdb@testdb01=# call dbms_datapump.stop_job(handle => dbms_datapump.attach(job_name => 'EXAMPLE001', job_owner => 'lightdb'));
CALL
lightdb@testdb01=# declare
    dbms_datapump.set_parameter(handle => hdl, name => 'COMPRESSION', value => 'ALL');
    dbms_datapump.set_parallel(handle => hdl, degree => 2);

lightdb@testdb01$#     hdl numeric;
lightdb@testdb01$#     jbs varchar2;
lightdb@testdb01$# begin
lightdb@testdb01$#     dbms_output.put_line('step 001 ' || systimestamp);
lightdb@testdb01$#     -- remote_link: 如果此参数的值非空，则它提供远程数据库的数据库链接名称，该数据库将成为当前作业的数据和元数据源。
lightdb@testdb01$#     hdl := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
lightdb@testdb01$#     dbms_output.put_line('handle = ' || hdl);
lightdb@testdb01$# 
lightdb@testdb01$#     dbms_output.put_line('step 002 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
lightdb@testdb01$#     dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => dbms_datapump.ku$_file_type_log_file);
lightdb@testdb01$# 
lightdb@testdb01$#     dbms_output.put_line('step 003 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.metadata_filter(handle => hdl, name => 'SCHEMA_EXPR', value => 'LIKE ''%bt%''');
lightdb@testdb01$#     dbms_datapump.metadata_filter(handle => hdl, name => 'NAME_EXPR', value => 'LIKE ''%1%''');
lightdb@testdb01$#     dbms_datapump.set_parameter(handle => hdl, name => 'COMPRESSION', value => 'ALL');
lightdb@testdb01$#     dbms_datapump.set_parallel(handle => hdl, degree => 2);
lightdb@testdb01$# 
lightdb@testdb01$#     dbms_output.put_line('step 004 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.start_job(handle => hdl);
lightdb@testdb01$# 
lightdb@testdb01$#     dbms_output.put_line('step 005 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
lightdb@testdb01$# 
lightdb@testdb01$#     dbms_output.put_line('step 006 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.stop_job(handle => hdl);
lightdb@testdb01$#     
lightdb@testdb01$#     dbms_output.put_line('step 007 ' || systimestamp);
lightdb@testdb01$# end;
lightdb@testdb01$# /
NOTICE:  export cmd:lt_dump -v --lt-exclude-lightdb-objects -d testdb01 -p 1991 -U lightdb -j 2 -n '*bt*' -t '(*bt*).(*1*)' -F d -f /home/lightdb/datapump/example001.dmp > /home/lightdb/datapump/example001.log 2>&1 & echo $!

DO
复制

drop table bt01.a1;
drop table bt01.s1;
drop table bt01.d1;
drop table bt01.f1;

declare
    hdl numeric;
    jbs varchar2;
begin
    dbms_output.put_line('step 001 ' || systimestamp);
    hdl := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
    dbms_output.put_line('handle = ' || hdl);
    dbms_output.put_line('step 002 ' || systimestamp);
    dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
    dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => dbms_datapump.ku$_file_type_log_file);
    dbms_datapump.set_parallel(handle => hdl, degree => 2);
    dbms_output.put_line('step 003 ' || systimestamp);
    dbms_datapump.start_job(handle => hdl);
    dbms_output.put_line('step 004 ' || systimestamp);
    dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
    dbms_output.put_line('step 005 ' || systimestamp);
    dbms_datapump.stop_job(handle => hdl);
    dbms_output.put_line('step 006 ' || systimestamp);
end;
/


lightdb@testdb01=# declare
lightdb@testdb01$#     hdl numeric;
lightdb@testdb01$#     jbs varchar2;
lightdb@testdb01$# begin
lightdb@testdb01$#     dbms_output.put_line('step 001 ' || systimestamp);
lightdb@testdb01$#     hdl := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
lightdb@testdb01$#     dbms_output.put_line('handle = ' || hdl);
lightdb@testdb01$#     dbms_output.put_line('step 002 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
lightdb@testdb01$#     dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => dbms_datapump.ku$_file_type_log_file);
lightdb@testdb01$#     dbms_datapump.set_parallel(handle => hdl, degree => 2);
lightdb@testdb01$#     dbms_output.put_line('step 003 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.start_job(handle => hdl);
lightdb@testdb01$#     dbms_output.put_line('step 004 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
lightdb@testdb01$#     dbms_output.put_line('step 005 ' || systimestamp);
lightdb@testdb01$#     dbms_datapump.stop_job(handle => hdl);
lightdb@testdb01$#     dbms_output.put_line('step 006 ' || systimestamp);
lightdb@testdb01$# end;
lightdb@testdb01$# /
NOTICE:  import cmd:lt_restore -v -d testdb01 -p 1991 -U lightdb -j 2 -F d /home/lightdb/datapump/example001.dmp > /home/lightdb/datapump/example001.log 2>&1 & echo $!
DO

select count(*) from bt01.a1;
select count(*) from bt01.s1;
select count(*) from bt01.d1;
select count(*) from bt01.f1;
复制