BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_JOBS view tips

Oracle Tips by Burleson Consulting

DBA_JOBS

<< Return to the index

Oracle 11g makes the following comments about the DBA_JOBS table: "All jobs in the database"

Related notes on DBA_JOBS:

Column description of the DBA_JOBS view:

JOB

Description of DBA_JOBS.JOB: "Identifier of job. Neither import/export nor repeated executions change it."

LOG_USER

Description of DBA_JOBS.LOG_USER: "USER who was logged in when the job was submitted"

PRIV_USER

Description of DBA_JOBS.PRIV_USER: "USER whose default privileges apply to this job"

SCHEMA_USER

Description of DBA_JOBS.SCHEMA_USER: "select * from bar means select * from schema_user.bar "

LAST_DATE

Description of DBA_JOBS.LAST_DATE: "Date that this job last successfully executed"

LAST_SEC

Description of DBA_JOBS.LAST_SEC: "Same as LAST_DATE. This is when the last successful execution started."

THIS_DATE

Description of DBA_JOBS.THIS_DATE: "Date that this job started executing (usually null if not executing)"

THIS_SEC

Description of DBA_JOBS.THIS_SEC: "Same as THIS_DATE. This is when the last successful execution started."

NEXT_DATE

Description of DBA_JOBS.NEXT_DATE: "Date that this job will next be executed"

NEXT_SEC

Description of DBA_JOBS.NEXT_SEC: "Same as NEXT_DATE. The job becomes due for execution at this time."

TOTAL_TIME

Description of DBA_JOBS.TOTAL_TIME: "Total wallclock time spent by the system on this job, in seconds"

BROKEN

Description of DBA_JOBS.BROKEN: "If Y, no attempt is being made to run this job. See dbms_jobq.broken(job)."

INTERVAL

Description of DBA_JOBS.INTERVAL: "A date function, evaluated at the start of execution, becomes next NEXT_DATE"

FAILURES

Description of DBA_JOBS.FAILURES: "How many times has this job started and failed since its last success?"

WHAT

Description of DBA_JOBS.WHAT: "Body of the anonymous PL/SQL block that this job executes"

NLS_ENV

Description of DBA_JOBS.NLS_ENV: "alter session parameters describing the NLS environment of the job"

MISC_ENV

Description of DBA_JOBS.MISC_ENV: "a versioned raw maintained by the kernel, for other session parameters"

INSTANCE

Description of DBA_JOBS.INSTANCE: "Instance number restricted to run the job"

DBA_JOBS View Source

Oracle 11g's data dictionary defines the DBA_JOBS view using the following source query:

select JOB, lowner LOG_USER, powner PRIV_USER, cowner SCHEMA_USER,
LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
NEXT_DATE, substr(to_char(next_date,'HH24:MI:SS'),1,8) NEXT_SEC,
(total+(sysdate-nvl(this_date,sysdate)))*86400 TOTAL_TIME,
decode(mod(FLAG,2),1,'Y',0,'N','?') BROKEN,
INTERVAL# interval, FAILURES, WHAT,
nlsenv NLS_ENV, env MISC_ENV, j.field1 INSTANCE
from sys.job$ j
where BITAND(j.scheduler_flags, 2) IS NULL OR
BITAND(j.scheduler_flags, 2) = 0 /* don't show jobs with drop flag */

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books