Search This Blog

Total Pageviews

Monday 16 January 2012

Oracle Job logs

DBMS_SCHEDULER FAILS log
Oracle SCHEDULER log
SCHEDULER log


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
-- AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;

Generate DDL for scheduler jobs

 


Generate DDL for scheduler jobs

dbms_metadata job




set long 1000000000 pagesize 0 trimspool on

select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',JOB_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_scheduler_jobs
where JOB_TYPE is not null
and owner !='SYS';


or select from dba_objects

set long 1000000000 pagesize 0 trimspool on

select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',object_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_objects
where object_type='JOB'
and owner !='SYS';




or

expdp \"/ as sysdba\"  dumpfile=scott_job.dmp SCHEMAS=SCOTT content=metadata_only  include=JOB



 



****************************************************************************************************

Dbms_metadata.Get_ddl Can Not Capture Ddl For Scheduler Jobs [ID 567504.1] for sys owner ????????

not work for owner ='SYS'; no idea why !!!!!!!!!!!!!!!!!!!!


as per metalink <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Currently the DBMS_METADATA does not support the new 'job' type created by DBMS_SCHEDULER.


********************************************************************************************************

Oracle DBA

anuj blog Archive