写的是一个记录大量数据量的一个日记服务器: 其中涉及到一个表以后可能要记录大量的数据,就考虑了用分表来记录数据,
写了存储过程每天生产一张表,其中大量是在网上找的资料,自己整理了下。
create or replace procedure createlogs_time
Authid Current_User
as
tabname varchar(200);
begin
select 'ex_log' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
--create table tabname as select * from tbl_programme where 1 != 1;
execute immediate 'create table ' || tabname ||' tablespace manage as select * from TMON_MONITOR_LOG_EXCLOG where 1 != 1';
commit;
end;
job是让这个存储过程每天的晚上八点执行
variable job2 number ;
begin
dbms_job.submit(:job2,'createlogs_time;',trunc(sysdate+)+20/24,'trunc(sysdate+)+20/24');
commit;
end ;
然后要把每天生产的这张表的一些信息统计 写了存储过程
create or replace procedure updateC is
type rc is ref cursor;
l_cursor rc;
v_sql varchar2(1000);
v_tab_name varchar2(30);
times varchar(20);
log_time varchar(40);
res_name varchar(40);
res_req_times varchar(40);
begin
select to_char(sysdate, 'yyyymmdd') into times from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into log_time from dual;
--v_tab_name := 'ex_log20090828';
select 'ex_log' || to_char(sysdate,'yyyymmdd') into v_tab_name from dual;
v_sql:= 'select res_name, count(req_type) as resSum from ' || v_tab_name || ' where req_type=''finish'' group by res_name';
open l_cursor for v_sql;
loop
fetch l_cursor into res_name,res_req_times;
exit when l_cursor%notfound;
insert into TMON_monitor_count_exlog values(test.nextval,times, res_name, log_time,res_req_times,res_req_times);
end loop;
close l_cursor ;
end;
job每天晚上的八点执行这个存储过程
variable job2 number ; begin dbms_job.submit(:job2,'updateC;',trunc(sysdate+)+20/24,'trunc(sysdate+)+20/24'); commit; end ;