Scheduler jobs are executed with service name:SYS$USERS
begin
dbms_scheduler.drop_job ('TEST_JOB1');
end;
/
BEGIN dbms_scheduler.create_job ('TEST_JOB1', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE l_sql VARCHAR2(3999) := ''''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => ''DBMS_SCHEDULER'',action_name => ''CE_STREAM''); DBMS_OUTPUT.PUT_LINE(''This is a test job''); END;', number_of_arguments => 0, start_date => systimestamp, repeat_interval => null, end_date => null, job_class => 'DEFAULT_JOB_CLASS', enabled => false, auto_drop => false, comments => 'A test Job ' ); END;
/
Session 1:
------------------
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
select sysdate from dual;
BEGIN
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'SYS$USERS', module_name=>'DBMS_SCHEDULER', action_name=>'CE_STREAM',waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');
END;
/
Session 2:
------------------
BEGIN
dbms_scheduler.enable(name => 'TEST_JOB1');
END;
/
session 1:
-----------------------
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
select sysdate from dual;
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=> 'SYS$USERS', module_name=>'DBMS_SCHEDULER', action_name=>'CE_STREAM');
select * from all_Scheduler_jobs where job_name='TEST_JOB1';
select * From all_scheduler_running_jobs ;
select * from all_Scheduler_job_run_Details where job_name='TEST_JOB1';
select * from gv$session where module ='DBMS_SCHEDULER';
select * from gV$ACTIVE_SESSION_HISTORY where module ='DBMS_SCHEDULER';
select * from dba_hist_active_sess_history where module ='DBMS_SCHEDULER';
col ADR_HOME for a60
col trace_filename for a80
set lines 400
set timing on;
set feedback on;
select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents
where regexp_like (payload, 'DBMS_SCHEDULER|CE_STREAM|TM_XMD_' )
and trace_filename like '%.trc'
--and component_name='SQL_Trace'
and TIMESTAMP > sysdate-1;
order by inst_id;
col ADR_HOME for a60
col trace_filename for a80
set lines 400
set timing on;
set feedback on;
SELECT DISTINCT
inst_id,
adr_home,
trace_filename
FROM
gv$diag_trace_file_contents
WHERE
REGEXP_LIKE ( payload,
'DBMS_SCHEDULER|CE_STREAM|TM_XMD_' )
AND trace_filename LIKE '%.trc'
AND timestamp > sysdate - 1;
order
by inst_id;
select PAYLOAD from gv$diag_trace_file_contents
where trace_filename='DBNAME_ora_6338.trc'
and inst_id=1
and adr_home='..'
order by line_number
-----------------
grep -i CE_STREAM *.trc
schedpoc3_j000_14008.trc:*** ACTION NAME:(CE_STREAM) 2021-08-04T05:33:43.715390-07:00
schedpoc3_j000_14008.trc:DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(128) := :job_name; job_subname VARCHAR2(128) := :job_subname; job_owner VARCHAR2(128) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner VARCHAR2(128) := :credown; credential_name VARCHAR2(128) := :crednam; destination_owner VARCHAR2(128) := :destown; destination_name VARCHAR2(128) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN DECLARE l_sql VARCHAR2(3999) := ''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => 'DBMS_SCHEDULER',action_name => 'CE_STREAM'); DBMS_OUTPUT.PUT_LINE('This is a test job'); END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
[mkusukun@denaa433 trace]$
tkprof schedpoc3_j000_14008.trc schedpoc3_j000_14008.log sys=no sort=EXEELA waits=yes aggregate=no
