背景说明：在当前数据库中，每半个小时创建一张基于时间点的表，表内容来自于另一个数据库orcl中shall.Scott_Emp。
1.当前数据库准备TNS
----准备另一个数据库orcl的tns，后面好创建db_link
[oracle@zyx ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora
db_orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.创建DB_LINK
----当前数据库创建连接另一数据库的link
create public database link db_orcl_link connect to shall identified by shall using 'db_orcl';
----测试：
sys@ORCL>select count(*) from Scott_Emp@db_orcl_link;
COUNT(*)
----------
14
3.创建存储过程
----先创建一张表，用于存放我们每半个小时生成的表名，不然很难知道到底有哪些表
create table temp_table (tablename varchar2(100));
----基于时间点创建表名，内容来自于数据库orcl中shall.Scott_Emp
create or replace procedure p_Scott_Emp_bak is
v_date varchar2(16);
v_sql varchar2(2000);
v_tablename varchar2(20);
begin
select to_char(sysdate, 'yyyymmddhh24mi') into v_date from dual;
v_tablename := 't_' || v_date;
v_sql := 'create table '|| v_tablename ||' as select * from Scott_Emp@db_orcl_link';
dbms_output.put_line(v_sql);
execute immediate v_sql;
insert into temp_table values ('t_'||v_date);
commit;
/
----调用存储过程进行测试
sys@ORCL>set serveroutput on
sys@ORCL>exec p_Scott_Emp_bak;
create table t_201604251058 as select * from Scott_Emp@db_orcl_link
PL/SQL procedure successfully completed.
sys@ORCL>select * from temp_table;
TABLENAME
----------------------------------------------------------------------------------------------------
t_201604251058
sys@ORCL>select count(*) from t_201604251058;
COUNT(*)
----------
14
4.创建job
----创建JOB每30分钟执行一次
sys@ORCL>var job1 number
sys@ORCL>exec dbms_job.submit(:job1,'p_Scott_Emp_bak;',sysdate,'sysdate+30/1440');
PL/SQL procedure successfully completed.
----手动运行一次
sys@ORCL>exec dbms_job.run(:job1);
create table t_201604251111 as select * from Scott_Emp@db_orcl_link
PL/SQL procedure successfully completed.
sys@ORCL>select * from temp_table;
TABLENAME
----------------------------------------------------------------------------------------------------
t_201604251058
t_201604251111
sys@ORCL>select count(*) from t_201604251111;
COUNT(*)
----------
14
sys@ORCL>select JOB,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT from dba_jobs where what='p_Scott_Emp_bak;';
JOB LAST_DATE NEXT_DATE BROKEN INTERVAL WHAT
----- ----------------- ---------------------- ------------------ --------------------- -----------------
43 2016-04-25 11:11:46 2016-04-25 11:41:46 N sysdate+30/1440 p_Scott_Emp_bak;
----后续：
sys@ORCL>select sysdate from dual;
SYSDATE
-------------------
2016-04-25 11:17:19
——————————————————————>>>
sys@ORCL>select sysdate from dual;
SYSDATE
-------------------
2016-04-25 13:41:18
sys@ORCL>select * from temp_table;
TABLENAME
----------------------------------------------------------------------------------------------------
t_201604251058
t_201604251111
t_201604251141
t_201604251211
t_201604251241
t_201604251311
6 rows selected.
sys@ORCL>select count(*) from t_201604251311;
COUNT(*)
----------
14
sys@ORCL>select JOB,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT from dba_jobs where what='p_Scott_Emp_bak;';
JOB LAST_DATE NEXT_DATE BROKEN INTERVAL WHAT
----------------------------------------------------------------------------------------------------
43 2016-04-25 13:41:55 2016-04-25 14:11:55 N sysdate+30/1440 p_Scott_Emp_bak;