dbms_scheduler
select owner,job_name,program_name from dba_scheduler_jobs;
停止启动分析:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
查询创建好的PROGRAM:
SQL> SELECT PROGRAM_NAME,PROGRAM_ACTION FROM DBA_SCHEDULER_PROGRAMS
2 WHERE PROGRAM_NAME='GATHER_STATS_PROG'
3 ;
PROGRAM_NAME PROGRAM_ACTION
------------------------------ --------------------------------------------------------------------------------
GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc
SQL>
创建PROGRAM的过程:
BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'test_plsql_block_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');
-- Shell Script.
DBMS_SCHEDULER.create_program (
program_name => 'test_executable_prog',
program_type => 'EXECUTABLE',
program_action => '/u01/app/oracle/dba/gather_scott_stats.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics us a shell script.');
-- Stored Procedure with Arguments.
DBMS_SCHEDULER.create_program (
program_name => 'test_stored_procedure_prog',
program_type => 'STORED_PROCEDURE',
program_action => 'DBMS_STATS.gather_schema_stats',
number_of_arguments => 1,
enabled => FALSE,
comments => 'Program to gather SCOTT''s statistics using a stored procedure.');
DBMS_SCHEDULER.define_program_argument (
program_name => 'test_stored_procedure_prog',
argument_name => 'ownname',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => 'SCOTT');
DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog');
END;
/[@more@]
select owner,job_name,program_name from dba_scheduler_jobs;
停止启动分析:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
查询创建好的PROGRAM:
SQL> SELECT PROGRAM_NAME,PROGRAM_ACTION FROM DBA_SCHEDULER_PROGRAMS
2 WHERE PROGRAM_NAME='GATHER_STATS_PROG'
3 ;
PROGRAM_NAME PROGRAM_ACTION
------------------------------ --------------------------------------------------------------------------------
GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc
SQL>
创建PROGRAM的过程:
BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'test_plsql_block_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');
-- Shell Script.
DBMS_SCHEDULER.create_program (
program_name => 'test_executable_prog',
program_type => 'EXECUTABLE',
program_action => '/u01/app/oracle/dba/gather_scott_stats.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics us a shell script.');
-- Stored Procedure with Arguments.
DBMS_SCHEDULER.create_program (
program_name => 'test_stored_procedure_prog',
program_type => 'STORED_PROCEDURE',
program_action => 'DBMS_STATS.gather_schema_stats',
number_of_arguments => 1,
enabled => FALSE,
comments => 'Program to gather SCOTT''s statistics using a stored procedure.');
DBMS_SCHEDULER.define_program_argument (
program_name => 'test_stored_procedure_prog',
argument_name => 'ownname',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => 'SCOTT');
DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog');
END;
/[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18921899/viewspace-1017717/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18921899/viewspace-1017717/