LightDB支持DBMS_DATAPUMP包

DBMS_DATAPUMP 是 Oracle 数据库中的一个 PL/SQL 包，用于实现数据泵功能，即数据的高性能导入和导出。这个包提供了一组存储过程和函数，可以用来管理数据泵作业、导出和导入数据、查询作业状态等操作。通过使用DBMS_DATAPUMP，用户可以高效地在Oracle数据库之间移动大量数据，并具有灵活的控制和监视功能。

使用DBMS_DATAPUMP的一般步骤：

1. 创建数据泵作业：使用DBMS_DATAPUMP包中的CREATE_JOB过程创建一个数据泵作业。可以指定导出或导入作业的参数，例如作业名称、导出目录、导入目录等。

2. 定义作业操作：使用ADD_FILE、ADD_FILELIST、ADD_DIRECTORY等过程定义作业的操作。可以添加导出或导入的文件、目录、表等。

3. 启动作业：使用START_JOB过程启动先前定义的数据泵作业。可以选择导出、导入或同时导出和导入数据。

4. 监视作业：使用GET_STATUS过程监视作业的状态和进度。可以查看作业是否正在运行、已完成或出现错误。

5. 完成作业：使用FINISH_JOB过程完成数据泵作业。可以标记作业为成功完成或取消。

6. 清理作业：使用PURGE过程清理已完成的作业，从数据泵的元数据中删除作业信息。

LightDB在2024RP1版本里面，将支持DBMS_DATAPUMP包。
具体function和procedure支持情况如下：

-- OPERATION已支持'EXPORT','IMPORT';JOB_MODE已支持'SHCEMA','TABLE'
FUNCTION OPEN(OPERATION IN VARCHAR2, JOB_MODE IN VARCHAR2, REMOTE_LINK IN VARCHAR2 DEFAULT NULL, JOB_NAME IN VARCHAR2 DEFAULT NULL, VERSION IN VARCHAR2 DEFAULT 'COMPATIBLE') RETURN NUMERIC;

FUNCTION ATTACH(JOB_NAME IN VARCHAR2 DEFAULT NULL, JOB_OWNER IN VARCHAR2 DEFAULT NULL) RETURN NUMERIC;
--FILETYPE已支持KU$_FILE_TYPE_DUMP_FILE,KU$_FILE_TYPE_LOG_FILE 
PROCEDURE ADD_FILE(HANDLE IN NUMERIC, FILENAME IN VARCHAR2, DIRECTORY IN VARCHAR2, FILESIZE IN VARCHAR2 DEFAULT NULL, FILETYPE IN NUMERIC DEFAULT 0, REUSEFILE IN NUMERIC DEFAULT NULL);

PROCEDURE SET_PARAMETER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN VARCHAR2);

PROCEDURE SET_PARAMETER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN NUMERIC);

PROCEDURE SET_PARALLEL(HANDLE IN NUMERIC, DEGREE IN NUMERIC);
--NAME已支持SCHEMA_EXPR,NAME_EXPR;VALUE已支持IN,NOT IN,LIKE,NOT LIKE表达式
PROCEDURE METADATA_FILTER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN VARCHAR2, OBJECT_PATH IN VARCHAR2 DEFAULT NULL);

PROCEDURE METADATA_FILTER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN CLOB, OBJECT_PATH IN VARCHAR2 DEFAULT NULL);

PROCEDURE START_JOB(HANDLE IN NUMERIC, SKIP_CURRENT IN NUMERIC DEFAULT 0, ABORT_STEP IN NUMERIC DEFAULT 0, CLUSTER_OK IN NUMERIC DEFAULT 1, SERVICE_NAME IN VARCHAR2 DEFAULT NULL);

PROCEDURE STOP_JOB(HANDLE IN NUMERIC, IMMEDIATE IN NUMERIC DEFAULT 0, KEEP_MASTER IN NUMERIC DEFAULT NULL, DELAY IN NUMERIC DEFAULT 60);

PROCEDURE WAIT_FOR_JOB(HANDLE IN NUMERIC, JOB_STATE OUT VARCHAR2);

使用案例：

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

create schema s01;
grant usage on schema s01 to public;

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

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

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

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

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

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

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

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

select count(*) from s01.a1; --10000000
select count(*) from s01.s1; --10000000
select count(*) from s01.d1; --10000000
select count(*) from s01.f1; --10000000

select count(*) from s01.g2; --10000000
select count(*) from s01.h2; --10000000
select count(*) from s01.j2; --10000000
select count(*) from s01.k2; --10000000

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

select dbms_output.serveroutput('t');
--按元数据过滤条件导出数据
declare
    hdl numeric;
    jbs varchar2;
begin
    hdl := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');

    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);
    --metadata_filter中的name可选项类型与open中的job_mode需要保持一致
    --即NAME_EXPR对应TABLE,SCHEMA_EXPR对应SCHEMA
    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_datapump.start_job(handle => hdl);

    dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);

    dbms_datapump.stop_job(handle => hdl);
end;
/

--清除数据DBMS_DATAPUMP导入
drop table s01.a1;
drop table s01.s1;
drop table s01.d1;
drop table s01.f1;
--导入上一步导出的example001.dmp
declare
    hdl numeric;
    jbs varchar2;
begin
    hdl := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');

    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_datapump.start_job(handle => hdl);

    dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);

    dbms_datapump.stop_job(handle => hdl);
end;
/

select count(*) from s01.a1; --10000000
select count(*) from s01.s1; --10000000
select count(*) from s01.d1; --10000000
select count(*) from s01.f1; --10000000

--在job_name已存在或异常中断后不知道handle情况下可通过dbms_datapump.attach()获取handle然后带入dbms_datapump.stop_job()清除配置数据
call dbms_datapump.stop_job(handle => dbms_datapump.attach(job_name => 'example001', job_owner => 'lightdb'));

详情可参考LightDB官网查看：
LightDB: 更快、更稳、更懂金融的分布式关系型数据库