Study Notes: Oracle + procedure + job

Source: Internet
Author: User

Write is a log server that records a large amount of data: When a table is involved, it may need to record a large amount of data, so we have to consider using Table shards to record data,

I wrote a storage process to produce a table every day. A large amount of data was found online and I sorted it out myself.



Create or replace procedure createlogs_time
Tabname varchar (200 );
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 ';



Job is used to execute the stored procedure at every day.


Variable job2 number;
Dbms_job.submit (: job2, 'createlogs _ time; ', trunc (sysdate +) + 20/24, 'trunc (sysdate +) + 20/24 ');



Then we need to write the statistics of the table produced every day into the stored procedure.

Create or replace procedure updatec is

Type RC is ref cursor;
Rochelle 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 );
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;
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;


Job executes the stored procedure at every night


Variable job2 number;
Dbms_job.submit (: job2, 'updatec; ', trunc (sysdate +) + 20/24, 'trunc (sysdate +) + 100'); Commit;





Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.