kettle怎么复制资源库的job_kettle资源库备份

存储过程：

在本地使用dblink连接远程表创建物化视图，根据物化视图创建表

create or replace procedure backup_kettle

AUTHID CURRENT_USER

as

cursor tbl_cur is

select table_name as src_table from [email protected]_kettle

where 1 = 1 and owner = 'KETTLE' ;

v_src_table varchar2(64);

v_mat_name varchar2(64);

v_cnt_sql varchar2(1000);

v_status varchar2(20);

v_counts int;

begin

select status into v_status from [email protected]_kettle;

select count(1) into v_counts from R_JOB;

if v_status='OPEN' and v_counts >1 then

for row in (select mview_name from all_mviews where owner ='KETTLE' and mview_name like 'SYNC%')

loop

execute immediate 'drop MATERIALIZED view '||row.mview_name;

end loop;

for row in (select table_name from all_tables where owner ='KETTLE' and( table_name like 'R%' or table_name like 'EDW%'))

loop

execute immediate 'drop table '||row.table_name;

end loop;

open tbl_cur; -- 打开游标

loop

fetch tbl_cur into v_src_table; -- 取值

exit when tbl_cur%notfound; --当没有记录时退出循环

v_mat_name:='SYNC_'||v_src_table;

v_cnt_sql:='create MATERIALIZED view '||v_mat_name||' BUILD IMMEDIATE DISABLE QUERY REWRITE as

select * from [email protected]_kettle';

execute immediate v_cnt_sql;

DBMS_MVIEW.REFRESH('"'||v_mat_name||'"','C');

v_cnt_sql:= 'create table '||v_src_table||' as select * from '||v_mat_name;

execute immediate v_cnt_sql ;

commit;

end loop;

close tbl_cur; -- 关闭游标

end if;

end;

创建job定时执行该存储过程

declare

job number;

BEGIN

DBMS_JOB.SUBMIT(

JOB => job, /*自动生成JOB_ID*/

WHAT => 'backup_kettle;', /*需要执行的存储过程名称或SQL语句*/

NEXT_DATE => sysdate, /*初次执行时间-立即执行*/

INTERVAL => 'trunc(sysdate+1)+22*60/24*60+15/60' /*每隔1分钟执行一次*/

);

commit;

end;

ps:

select value from v$parameter where name like '%job_queue_processes%'

如果value的值是0 ，则定时任务不会执行，需改一下

alter system set job_queue_processes = 30 scope=MEMORY

scope说明：spfile、memory、both

spfile指重启数据库生效

memory指在内存中生效即立即生效，但重启后会失效

both指立即生效且永久有效

对于pfile启动的数据库 scope默认是MEMORY，如果指定memory和spfile会发生错误

对于spfile启动的数据库 scope默认是BOTH