Monday, July 6, 2009

Oracle Database Statistics on Tables, Indexes and Columns

Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 6, 2009

DBA_SCHEDULER_JOBS

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

========================================================================


DBA_SCHEDULER_JOBS displays information about all Scheduler jobs in the database. Its columns are the same as those in ALL_SCHEDULER_JOBS.
  • ALL_SCHEDULER_JOBS displays information about the Scheduler jobs accessible to the current user.
  • USER_SCHEDULER_JOBS displays information about the Scheduler jobs owned by the current user. This view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
Owner of the Scheduler job
JOB_NAME
VARCHAR2(30)
Name of the Scheduler job
JOB_SUBNAME
VARCHAR2(30)
Subname of the Scheduler job (for a job running a chain step)
JOB_STYLE
VARCHAR2(11)
Job style:
  • REGULAR
  • LIGHTWEIGHT
JOB_CREATOR
VARCHAR2(30)
Original creator of the job
CLIENT_ID
VARCHAR2(64)
Client identifier of the user creating the job
GLOBAL_UID
VARCHAR2(32)
Global user identifier of the user creating the job
PROGRAM_OWNER
VARCHAR2(4000)
Owner of the program associated with the job
PROGRAM_NAME
VARCHAR2(4000)
Name of the program associated with the job
JOB_TYPE
VARCHAR2(16)
Inline job action type:
  • PLSQL_BLOCK
  • STORED_PROCEDURE
  • EXECUTABLE
  • CHAIN
JOB_ACTION
VARCHAR2(4000)
Inline job action
NUMBER_OF_ARGUMENTS
NUMBER
Inline number of job arguments
SCHEDULE_OWNER
VARCHAR2(4000)
Owner of the schedule that the job uses (can be a window or a window group)
SCHEDULE_NAME
VARCHAR2(4000)
Name of the schedule that the job uses (can be a window or a window group)
SCHEDULE_TYPE
VARCHAR2(12)
Type of the schedule that the job uses:
  • IMMEDIATE - Start date and repeat interval are NULL
  • ONCE - Repeat interval is NULL
  • PLSQL - PL/SQL expression used as schedule
  • CALENDAR - Oracle calendaring expression used as schedule
  • EVENT - Event schedule
  • NAMED - Named schedule
  • WINDOW - Window used as schedule
  • WINDOW_GROUP - Window group used as schedule
START_DATE
TIMESTAMP(6) WITH TIME ZONE
Original scheduled start date of the job (for an inline schedule)
REPEAT_INTERVAL
VARCHAR2(4000)
Inline schedule PL/SQL expression or calendar string
EVENT_QUEUE_OWNER
VARCHAR2(30)
Owner of the source queue into which the event will be raised
EVENT_QUEUE_NAME
VARCHAR2(30)
Name of the source queue into which the event will be raised
EVENT_QUEUE_AGENT
VARCHAR2(256)
Name of the AQ agent used by the user on the event source queue (if it is a secure queue)
EVENT_CONDITION
VARCHAR2(4000)
Boolean expression used as the subscription rule for the event on the source queue
EVENT_RULE
VARCHAR2(65)
Name of the rule used by the coordinator to trigger the event-based job
END_DATE
TIMESTAMP(6) WITH TIME ZONE
Date after which the job will no longer run (for an inline schedule)
JOB_CLASS
VARCHAR2(30)
Name of the job class associated with the job
ENABLED
VARCHAR2(5)
Indicates whether the job is enabled (TRUE) or disabled (FALSE)
AUTO_DROP
VARCHAR2(5)
Indicates whether the job will be dropped when it has completed (TRUE) or not (FALSE)
RESTARTABLE
VARCHAR2(5)
Indicates whether the job can be restarted (TRUE) or not (FALSE)
STATE
VARCHAR2(15)
Current state of the job:
  • DISABLED
  • RETRY SCHEDULED
  • SCHEDULED
  • RUNNING
  • COMPLETED
  • BROKEN
  • FAILED
  • REMOTE
  • SUCCEEDED
  • CHAIN_STALLED
JOB_PRIORITY
NUMBER
Priority of the job relative to other jobs in the same class
RUN_COUNT
NUMBER
Number of times the job has run
MAX_RUNS
NUMBER
Maximum number of times the job is scheduled to run
FAILURE_COUNT
NUMBER
Number of times the job has failed to run
MAX_FAILURES
NUMBER
Number of times the job will be allowed to fail before being marked broken
RETRY_COUNT
NUMBER
Number of times the job has retried, if it is retrying
LAST_START_DATE
TIMESTAMP(6) WITH TIME ZONE
Last date on which the job started running
LAST_RUN_DURATION
INTERVAL DAY(9) TO SECOND(6)
Amount of time the job took to complete during the last run
NEXT_RUN_DATE
TIMESTAMP(6) WITH TIME ZONE
Next date on which the job is scheduled to run
SCHEDULE_LIMIT
INTERVAL DAY(3) TO SECOND(0)
Time after which a job which has not run yet will be rescheduled
MAX_RUN_DURATION
INTERVAL DAY(3) TO SECOND(0)
Maximum amount of time for which the job will be allowed to run
LOGGING_LEVEL
VARCHAR2(11)
Amount of logging that will be done pertaining to the job:
  • OFF
  • RUNS
  • FAILED RUNS
  • FULL
STOP_ON_WINDOW_CLOSE
VARCHAR2(5)
Indicates whether the job will stop if a window associated with the job closes (TRUE) or not (FALSE)
INSTANCE_STICKINESS
VARCHAR2(5)
Indicates whether the job is sticky (TRUE) or not (FALSE)
RAISE_EVENTS
VARCHAR2(4000)
List of job events to raise for the job:
  • JOB_STARTED
  • JOB_SUCCEEDED
  • JOB_FAILED
  • JOB_BROKEN
  • JOB_COMPLETED
  • JOB_STOPPED
  • JOB_SCH_LIM_REACHED
  • JOB_DISABLED
  • JOB_CHAIN_STALLED
  • JOB_OVER_MAX_DUR
SYSTEM
VARCHAR2(5)
Indicates whether the job is a system job (TRUE) or not (FALSE)
JOB_WEIGHT
NUMBER
Weight of the job
NLS_ENV
VARCHAR2(4000)
NLS environment of the job
SOURCE
VARCHAR2(128)
Source global database identifier
DESTINATION
VARCHAR2(128)
Destination global database identifier
CREDENTIAL_OWNER
VARCHAR2(30)
Owner of the credential to be used for an external job
CREDENTIAL_NAME
VARCHAR2(30)
Name of the credential to be used for an external job
INSTANCE_ID
NUMBER
Instance on which the user requests the job to run
DEFERRED_DROP
VARCHAR2(5)
Indicates whether the job will be dropped when completed due to user request (TRUE) or not (FALSE)
COMMENTS
VARCHAR2(240)
Comments on the job
FLAGS
NUMBER
This column is for internal use
Note:
1. Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the DBA_SCHEDULER_JOBS view:
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
2. The STATE column from DBA_SCHEDULER_JOBS provides information on the status of a particular job. It can be queried as follows:
SELECT job_name, state
FROM dba_scheduler_jobs
WHERE job_name='GATHER_STATS_JOB';
There are four types of jobs that are not running:
· FAILED
· BROKEN
· DISABLED
· COMPLETED
Note that if a job has recently completed successfully, but is scheduled to run again, the job state is set to 'SCHEDULED'. A job is marked as 'COMPLETED' if 'end_date' or 'max_runs' (in dba_scheduler_jobs) is reached.
Last updated: July 6, 2009