How to find out Oracle Scheduler /Job text
Oracle Job Text
on dbms_metadata.get_ddl
ORA-31603: object of type PROCOBJ not found in schema "SYS"
select dbms_metadata.get_ddl('PROCOBJ','FILE_WATCHER','SYS') from dual;
ERROR:
ORA-31603: object "FILE_WATCHER" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','MY_TEST_JOB', 'SCOTT') AS job_def FROM dual;
SELECT dbms_metadata.get_ddl('PROCOBJ','JOB_DEFAULT_USER', 'SCOTT') from dual;
==========================
for sys Job Metadata
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_PROC1','SYS') from dual;
ERROR:
ORA-31603: object "ANUJ_PROC1" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
Created a new user ...
SQL> create user C##TESTUSER identified by TESTUSER;
User created.
SQL> grant dba to C##TESTUSER ;
SQL> exec dbms_scheduler.copy_job('SYS.ANUJ_PROC1','C##TESTUSER.ANUJ_TEST');
PL/SQL procedure successfully completed.
set long 500
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','ANUJ_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"ANUJ_TEST"',
job_type=>'STORED_PROCEDURE', job_action=>
'SYS.ANUJ_PROC'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-FEB-2018 05.52.17.000000000 AM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=MINUTELY;INTERVAL=2'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
'Testing...'
);
sys.dbms_scheduler.set_attribute('"ANUJ_TEST"','NLS_ENV','NLS_LANGUAGE=''AMERICA
N'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NL
S_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-
RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.
SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=
''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR''
NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NC
HAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set serveroutput on
DECLARE
v_output CLOB := NULL;
v_owner VARCHAR2 (30) := 'SYS';
v_object_name VARCHAR2 (30) := 'ANUJ_PROC';
BEGIN
-- Note, we don't search for package bodies. We will extract the body
-- along with the package spec.
DBMS_OUTPUT.put_line ('Database DDL For Selected Objects Report');
FOR dd IN (SELECT owner, object_name, object_type FROM dba_objects
WHERE owner LIKE v_owner
AND object_name LIKE v_object_name
AND object_type IN ('PROCEDURE', 'PACKAGE', 'TRIGGER', 'FUNCTION'))
LOOP
SELECT DBMS_METADATA.get_ddl (dd.object_type, dd.object_name, dd.owner) INTO v_output FROM DUAL;
BEGIN
DBMS_OUTPUT.put_line (v_output);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unable to print code for object');
DBMS_OUTPUT.put_line ( dd.owner
|| '.'
|| dd.object_name
|| ' type: '
|| dd.object_type
);
END;
END LOOP;
END;
/
Database DDL For Selected Objects Report
CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."ANUJ_PROC" AS
BEGIN
insert
into SYS.TEST values ( 'AAAAAAA',sysdate) ;
commit;
END;
==================
2. Example !!!!
set linesize 300 pagesize 500
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
TTITLE 'Scheduled Tasks:'
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from dba_scheduler_job_run_details
where 1=1
and job_name like '%ANUJ%'
-- status <> 'SUCCEEDED'
order by actual_start_date;
job error
log# LOG_DATE job name step name step status code additional information
------- ----------------------------------- ------------------------------ -------------------- ------------ -------- ----------------------------------------
81068 10-FEB-18 04.00.01.149900 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
76472 09-FEB-18 04.00.00.950431 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
This job is failing
APEX_GATHER_JOB
set linesize 200 pagesize 500
col OWNER for a20
col OBJECT_NAME for a20
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='APEX_GATHER_JOB';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -----------------------
SYS APEX_GATHER_JOB JOB
exec dbms_scheduler.copy_job('SYS.APEX_GATHER_JOB','C##TESTUSER.APEX_GATHER_JOB_TEST');
set long 500
select dbms_metadata.get_ddl('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"APEX_GATHER_JOB_TEST"',
job_type=>'PLSQL_BLOCK', job_action=>
'begin dbms_stats.gather_schema_stats(ownname=>"APEX_050100", estimate_percent=>
10, cascade=>TRUE, no_invalidate=> TRUE); end;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('05-FEB-2018 04.00.00.000000000 PM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=DAILY; INTERVAL=1'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE
====
Another example !!!
create table scott.emp_job as select * from scott.emp;
Table created.
CREATE OR REPLACE PROCEDURE scott.emp_log as
begin
delete from scott.emp_job where HIREDATE < sysdate -15;
end emp_log;
Procedure created.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'SCOTT_EMP_LOG_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN scott.emp_log; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
enabled => TRUE); ----<<<<
END;
/
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a20
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB';
OWNER JOB_NAME ENABLED
-------------------- -------------------- ---------------
SYS SCOTT_EMP_LOG_JOB TRUE
--- for sys job !!!
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB', 'SYS') AS job_def FROM dual;
ORA-31603: object "SCOTT_EMP_LOG_JOB" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
======
copy the Job
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a30
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB_DUP';
OWNER JOB_NAME ENABLED
-------------------- ------------------------------ ---------------
SCOTT SCOTT_EMP_LOG_JOB_DUP FALSE
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB_DUP', 'SCOTT') AS job_def FROM dual;
JOB_DEF
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"SCOTT_EMP_LOG_JOB_DUP"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN scott.emp_log; END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('09-DEC-2021 03.38.43.238324000 AM -05:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0; bysecond=0;'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"SCOTT_EMP_LOG_JOB_DUP"','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERI
C_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''dd-mm-yy hh24:mi'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMEST
AMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY
'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set linesize 500 pagesize 300
col JOB_ACTION for a40
col SCHEDULE for a30
col SCHEMA_NAME for a25
col LAST_START_DATE for a25
col START_DATE for a25
select owner as schema_name,
job_name,
job_style,
case when job_type is null
then 'PROGRAM'
else job_type end as job_type,
start_date,
case when repeat_interval is null
then schedule_name
else repeat_interval end as schedule,
last_start_date,
next_run_date,
state,
case when job_type is null
then program_name
else job_action end as job_action
from dba_scheduler_jobs
where 1=1
--and JOB_NAME='SCOTT_EMP_LOG_JOB'
order by owner, job_name;
SCHEMA_NAME JOB_NAME JOB_STYLE JOB_TYPE START_DATE SCHEDULE LAST_START_DATE NEXT_RUN_DATE STATE JOB_ACTION
------------------------- ------------------------------ ----------------- ---------------- ------------------------- ------------------------------ ------------------------- --------------------------------------------------------------------------- --------------- ----------------------------------------
SYS SCOTT_EMP_LOG_JOB REGULAR PLSQL_BLOCK 09-DEC-21 03.38.43.238324 freq=hourly; byminute=0; bysec 09-DEC-21 04.00.00.527244 09-DEC-21 05.00.00.533142 AM -05:00 SCHEDULED BEGIN scott.emp_log; END;
AM -05:00 ond=0; AM -05:00
