w_status := sys.DBMS_LOCK.release (lockhandle => w_handle);
ELSE
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE, 'dd hh24:mi:ss')
|| CASE w_status
WHEN 1 THEN ': Lock wait timed out'
WHEN 2 THEN ': deadlock detected'
WHEN 3 THEN ': parameter error'
WHEN 4 THEN ': already holding lock'
WHEN 5 THEN ': illegal lock handle'
ELSE ': unknown error'
END);
END IF;
END;
/
5) 创建用于job调度的过程
BEGIN
DBMS_SCHEDULER.create_program (program_name => 'PROC_RAC_STATSPACK',
program_type => 'STORED_PROCEDURE',
program_action => 'db_proc_rac_statspack',
enabled => TRUE);
END;
/
6) 清除同名job(如果存在)
BEGIN
DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_COLLECT_N1',force=>true);
DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_COLLECT_N2',force=>true);
DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_PURGE_N1',force=>true);
DBMS_SCHEDULER.drop_job ('ORCL10_PERFSTAT_PURGE_N2',force=>true);
END;
/
7) 创建产生snapshot以及清除历史snapshot的job --Author ：Leshami --Blog ：
DBMS_SCHEDULER.create_job (
job_name => 'ORCL_PERFSTAT_COLLECT_N1',
program_name => 'PROC_RAC_STATSPACK',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=hourly; INTERVAL=1; BYMINUTE=30',
job_class => 'statspack_suse11a_class',
comments => 'This job will run on suse11a',
ENABLED => TRUE);
DBMS_SCHEDULER.create_job (
job_name => 'ORCL_PERFSTAT_PURGE_N1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin STATSPACK.PURGE(31); end;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=30',
job_class => 'statspack_suse11a_class',
enabled => TRUE);
END;
/
--- create the job for Node 2: