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@]

oracle10g自動分析任務

相關文章