Search This Blog

Total Pageviews

Tuesday, 17 April 2012

Oracle Job metadata

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






 

No comments:

Oracle DBA

anuj blog Archive