EMP
DEPT
[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: EMP Completed
Table: DEPT Completed
Saturday, February 6, 2010
Script to gather Stats for list of tables
Store the list of tables in tablelist.lst file.
EMP
DEPT
[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: EMP Completed
Table: DEPT Completed
EMP
DEPT
[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: EMP Completed
Table: DEPT Completed
/dev/null significance and STDIN, STDOUT, STDERR
sample > /dev/null 2>&1
There are three standard sources of input and output for a program. Standard input usually comes from the keyboard if it’s an interactive program, or from another program if it’s processing the other program’s output. The program usually prints to standard output, and sometimes prints to standard error. These three file descriptors (you can think of them as “data pipes”) are often called STDIN, STDOUT, and STDERR. The built-in numberings for them are 0, 1, and 2, in that order. By default, if you don’t name or number one explicitly, you’re talking about STDOUT.
The command above is redirecting standard output into /dev/null, which is a place you can dump anything you don’t want (often called the bit-bucket), then redirecting standard error into standard output (you have to put an & in front of the destination when you do this).
All output from this command should be shoved into a black hole. 2 > &1 will redirect STDERR to STDOUT which in turn is redirected to a BLACK HOLE
There are three standard sources of input and output for a program. Standard input usually comes from the keyboard if it’s an interactive program, or from another program if it’s processing the other program’s output. The program usually prints to standard output, and sometimes prints to standard error. These three file descriptors (you can think of them as “data pipes”) are often called STDIN, STDOUT, and STDERR. The built-in numberings for them are 0, 1, and 2, in that order. By default, if you don’t name or number one explicitly, you’re talking about STDOUT.
The command above is redirecting standard output into /dev/null, which is a place you can dump anything you don’t want (often called the bit-bucket), then redirecting standard error into standard output (you have to put an & in front of the destination when you do this).
All output from this command should be shoved into a black hole. 2 > &1 will redirect STDERR to STDOUT which in turn is redirected to a BLACK HOLE
Verify That CRS and RAC Use Same Interconnect Network
For the most efficient network detection and failover, CRS and RAC should use the same interconnect subnet so that they share the same view of connections and accessibility. To verify the interconnect subnet used by RAC, run the Oracle ORADEBUG utility on one of the instances:
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG IPC
Information written to trace file.
SQL> ORADEBUG tracefile_name
/u01/app/oracle/admin/prod/udump/prod1_ora_24409.trc
In the trace file, examine the SSKGXPT section to determine the subnet used by RAC. In this example, the subnet in use is 192.168.0.3 and the protocol used is UDP:
SSKGXPT 0xd7be26c flags info for network 0
socket no 7 IP 192.168.0.3 UDP 14727
To verify the interconnect subnet used by CRS, examine the value of the keyname SYSTEM.css.node_numbers.node.privatename in OCR:
prompt> ocrdump -stdout -keyname SYSTEM.css.node_numbers
[SYSTEM.css.node_numbers.node1.privatename]
ORATEXT : halinux03ic0
…
[SYSTEM.css.node_numbers.node2.privatename]
ORATEXT : halinux04ic0
The hostnames (halinux03ic0 and halinux04ic0 in this example) should match the subnet in the trace file produced by ORADEBUG (subnet 192.168.0.3). Use operating system tools to verify. For example, on Linux:
prompt> getent hosts halinux03ic0
192.168.0.3 halinux03ic0.us.oracle.com halinux03ic0
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG IPC
Information written to trace file.
SQL> ORADEBUG tracefile_name
/u01/app/oracle/admin/prod/udump/prod1_ora_24409.trc
In the trace file, examine the SSKGXPT section to determine the subnet used by RAC. In this example, the subnet in use is 192.168.0.3 and the protocol used is UDP:
SSKGXPT 0xd7be26c flags info for network 0
socket no 7 IP 192.168.0.3 UDP 14727
To verify the interconnect subnet used by CRS, examine the value of the keyname SYSTEM.css.node_numbers.node
prompt> ocrdump -stdout -keyname SYSTEM.css.node_numbers
[SYSTEM.css.node_numbers.node1.privatename]
ORATEXT : halinux03ic0
…
[SYSTEM.css.node_numbers.node2.privatename]
ORATEXT : halinux04ic0
The hostnames (halinux03ic0 and halinux04ic0 in this example) should match the subnet in the trace file produced by ORADEBUG (subnet 192.168.0.3). Use operating system tools to verify. For example, on Linux:
prompt> getent hosts halinux03ic0
192.168.0.3 halinux03ic0.us.oracle.com halinux03ic0
Thursday, February 4, 2010
srvctl command summary
1) srvctl add
Adds the node applications, database, database instance, ASM instance, or service.
2) srvctl remove
Removes the node applications, database, database instance, ASM instance, or service.
3) srvctl config
Lists the configuration for the node applications, database, ASM instance, or service.
4) srvctl enable
Enables the database, database instance, ASM instance, or service.
5) srvctl disable
Disables the database, database instance, ASM instance, or service.
6) srvctl start
Starts the node applications, database, database instance, ASM instance, or service.
7) srvctl stop
Stops the node applications, database, database instance, ASM instance, or service.
8) srvctl modify
Modifies the node applications, database, database instance, or service configuration.
9) srvctl relocate
Relocates the service from one instance to another.
10) srvctl status
Obtains the status of the node applications, database, database instance, ASM instance, or service.
11) srvctl getenv
Displays the environment variable in the configuration for the node applications, database, database instance, or service.
12) srvctl setenv and unsetenv
Sets and unsets the environment variable in the configuration for the node applications, database, database instance, or service.
Adds the node applications, database, database instance, ASM instance, or service.
2) srvctl remove
Removes the node applications, database, database instance, ASM instance, or service.
3) srvctl config
Lists the configuration for the node applications, database, ASM instance, or service.
4) srvctl enable
Enables the database, database instance, ASM instance, or service.
5) srvctl disable
Disables the database, database instance, ASM instance, or service.
6) srvctl start
Starts the node applications, database, database instance, ASM instance, or service.
7) srvctl stop
Stops the node applications, database, database instance, ASM instance, or service.
8) srvctl modify
Modifies the node applications, database, database instance, or service configuration.
9) srvctl relocate
Relocates the service from one instance to another.
10) srvctl status
Obtains the status of the node applications, database, database instance, ASM instance, or service.
11) srvctl getenv
Displays the environment variable in the configuration for the node applications, database, database instance, or service.
12) srvctl setenv and unsetenv
Sets and unsets the environment variable in the configuration for the node applications, database, database instance, or service.
TRCROUTE
MONITORING PRIVATE NETWORKS: IN RAC
Oracle Support recommends you use this tool to monitor RAC private
networks. Create a file named private.net or look at the
Exampleprivate.net file and manually enter in the hostname or ipaddress
you wish to monitor. Each unix os uses slightly different arguments to
the traceroute command. Refer to Exampleprivate.net for examples for
each unix os.
you can find more info@
http://www.oracleutilities.com/OSUtil/trcroute.html
Oracle Support recommends you use this tool to monitor RAC private
networks. Create a file named private.net or look at the
Exampleprivate.net file and manually enter in the hostname or ipaddress
you wish to monitor. Each unix os uses slightly different arguments to
the traceroute command. Refer to Exampleprivate.net for examples for
each unix os.
you can find more info@
http://www.oracleutilities.com/OSUtil/trcroute.html
Wednesday, February 3, 2010
Useful commands Part 3
To check with the primary key
-------------------------------
select *from user_constraints where table_name='TABLE_NAME' and constrainT_type in ('P','U');
select *from user_constraints where table_name='tblequipment' and constrainT_type in ('P','U');
SELECT *FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='PK_EQ';
=======================================================================
Table names
------------------------
SQL> select table_name from user_tables;
=======================================================================
list out user names
----------------------------
SQL> select username from dba_users;
=======================================================================
Tablespace Location
-----------------------------
select tablespace_name,file_name from dba_data_files;
=======================================================================
For Audit_Parameter
SQL> show parameter audit;
=======================================================================
AUDIT VIEWS
SQL> SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
VIEW_NAME
------------------------------
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
=========================================================================
SQL> SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session;
=========================================================================
CHECK BACKGROUND USERS
--------------------------
SQL> select osuser,username,status,type from gv$session
where type != 'BACKGROUND';
SQL> set pages 999
SQL> set lines 132
SQL> /
=========================================================================
COLUMN username FORMAT A10
--COLUMN logoff_time FORMAT A10
--COLUMN extended_timestamp FORMAT A35
COLUMN logoff_lread FORMAT 999,999,990
COLUMN logoff_pread FORMAT 999,999,990
COLUMN logoff_lwrite FORMAT 999,999,990
COLUMN logoff_dlock FORMAT A10
break on report
compute sum of logoff_lread on report
compute sum of logoff_pread on report
compute sum of logoff_lwrite on report
SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session WHERE USERNAME='MAN_PROD';
clear columns //to clear formatting
========================================================================
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
24/12/2008 16:14:50
SQL> select trunc(sysdate) from dual;
TRUNC(SYSDATE)
-------------------
24/12/2008 00:00:00
commit;
SELECT sysdate,trunc(trunc(sysdate,'mm')-1,'mm'),trunc(sysdate,'mm') from dual;
=========================================================================
SQL> select ntimestamp# from sys.aud$ where rownum<5;> select timestamp# from sys.aud$ where rownum<5;> select count(*) from sys.aud$ where ntimestamp# select * FROM dba_profiles WHERE PROFILE='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
VERIFY_FUNCTION
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.
=========================================================================
select obj_name,status from dba_objects where object_name='VERIFY_FUNCTION';
SQL> DROP FUNCTION AUDIT_USER.VERIFY_FUNCTION;
Function dropped.
=========================================================================
SQL> create user suman identified by sumanred9 profile audit_profile;
User created.
=========================================================================
C:\>pscp -P 261 c:\rpms.zip spulicherla@advansrac1.advanstec.com:rpms.zip
spulicherla@advansrac1.advanstec.com's password:
Access denied
spulicherla@advansrac1.advanstec.com's password:
rpms.zip | 78751 kB | 504.8 kB/s | ETA: 00:00:00 | 100%
ssh -R 6075:localhost:6000 -p 260 -l spulicherla advansrac1.advanstec.com
==========================================================================
SQL> conn sys/erptrn1@erptrn1 as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn sys/erptrn1@erptrn11 as sysdba
Connected.
SQL> show user
USER is "SYS"
==================================================================
ssh -R 6075:localhost:6000 -p 261 -l spulicherla advansrac1.advanstec.com
yes
password: winter3
=========================================
SQL> SELECT NAME,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;
NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
DATA EXTERN 3000 2946
SQL> drop diskgroup data including contents;
Diskgroup dropped.
SQL> show parameter asm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_asm_allow_only_raw_disks boolean FALSE
asm_diskgroups string
asm_diskstring string /oradata/nfsdg/*
asm_power_limit integer 11\\corp
asm_preferred_read_failure_groups string
SQL> create diskgroup data external redundancy disk
2 '/oradata/nfsdg/disk1',
3 '/oradata/nfsdg/disk2';
=================================
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:01 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle ~]$ export ORACLE_HOME=$ASM_HOME
[oracle ~]$ export ORACLE_SID=+ASM1
[oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:30 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
-------------------------------
select *from user_constraints where table_name='TABLE_NAME' and constrainT_type in ('P','U');
select *from user_constraints where table_name='tblequipment' and constrainT_type in ('P','U');
SELECT *FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='PK_EQ';
=======================================================================
Table names
------------------------
SQL> select table_name from user_tables;
=======================================================================
list out user names
----------------------------
SQL> select username from dba_users;
=======================================================================
Tablespace Location
-----------------------------
select tablespace_name,file_name from dba_data_files;
=======================================================================
For Audit_Parameter
SQL> show parameter audit;
=======================================================================
AUDIT VIEWS
SQL> SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
VIEW_NAME
------------------------------
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
=========================================================================
SQL> SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session;
=========================================================================
CHECK BACKGROUND USERS
--------------------------
SQL> select osuser,username,status,type from gv$session
where type != 'BACKGROUND';
SQL> set pages 999
SQL> set lines 132
SQL> /
=========================================================================
COLUMN username FORMAT A10
--COLUMN logoff_time FORMAT A10
--COLUMN extended_timestamp FORMAT A35
COLUMN logoff_lread FORMAT 999,999,990
COLUMN logoff_pread FORMAT 999,999,990
COLUMN logoff_lwrite FORMAT 999,999,990
COLUMN logoff_dlock FORMAT A10
break on report
compute sum of logoff_lread on report
compute sum of logoff_pread on report
compute sum of logoff_lwrite on report
SELECT username, logoff_time, logoff_lread, logoff_pread,
logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session WHERE USERNAME='MAN_PROD';
clear columns //to clear formatting
========================================================================
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
24/12/2008 16:14:50
SQL> select trunc(sysdate) from dual;
TRUNC(SYSDATE)
-------------------
24/12/2008 00:00:00
commit;
SELECT sysdate,trunc(trunc(sysdate,'mm')-1,'mm'),trunc(sysdate,'mm') from dual;
=========================================================================
SQL> select ntimestamp# from sys.aud$ where rownum<5;> select timestamp# from sys.aud$ where rownum<5;> select count(*) from sys.aud$ where ntimestamp#
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
VERIFY_FUNCTION
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.
=========================================================================
select obj_name,status from dba_objects where object_name='VERIFY_FUNCTION';
SQL> DROP FUNCTION AUDIT_USER.VERIFY_FUNCTION;
Function dropped.
=========================================================================
SQL> create user suman identified by sumanred9 profile audit_profile;
User created.
=========================================================================
C:\>pscp -P 261 c:\rpms.zip spulicherla@advansrac1.advanstec.com:rpms.zip
spulicherla@advansrac1.advanstec.com's password:
Access denied
spulicherla@advansrac1.advanstec.com's password:
rpms.zip | 78751 kB | 504.8 kB/s | ETA: 00:00:00 | 100%
ssh -R 6075:localhost:6000 -p 260 -l spulicherla advansrac1.advanstec.com
==========================================================================
SQL> conn sys/erptrn1@erptrn1 as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn sys/erptrn1@erptrn11 as sysdba
Connected.
SQL> show user
USER is "SYS"
==================================================================
ssh -R 6075:localhost:6000 -p 261 -l spulicherla advansrac1.advanstec.com
yes
password: winter3
=========================================
SQL> SELECT NAME,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;
NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
DATA EXTERN 3000 2946
SQL> drop diskgroup data including contents;
Diskgroup dropped.
SQL> show parameter asm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_asm_allow_only_raw_disks boolean FALSE
asm_diskgroups string
asm_diskstring string /oradata/nfsdg/*
asm_power_limit integer 11\\corp
asm_preferred_read_failure_groups string
SQL> create diskgroup data external redundancy disk
2 '/oradata/nfsdg/disk1',
3 '/oradata/nfsdg/disk2';
=================================
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:01 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle ~]$ export ORACLE_HOME=$ASM_HOME
[oracle ~]$ export ORACLE_SID=+ASM1
[oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 9 15:11:30 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Tuesday, February 2, 2010
Oracle Scheduler in 10g
Oracle 10g introduced the Oracle Scheduler, a new feature that makes the scheduling of routine tasks a simple matter. Oracle Scheduler is a major advancement over the old DBMS_JOB scheduling system found in previous Oracle versions.
SQL> select owner, job_name, state from dba_scheduler_jobs;
To enable an object, use DBMS_SCHEDULER.ENABLE procedure. The procedure accepts only one argument, NAME, which designates one of the following:
The name of specific object
A comma-seperated list of objects
SQL> begin
dbms_scheduler.enable(‘BACKUP_JOB’);
end;
SQL> /
To enable multiple objects, comma-seperated list can be passed in. Note that the entire list is enclosed in single quotes.
SQL> begin
dbms_scheduler.enable(‘BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB’);
end;
SQL> /
The list of objects can also contain both groups and individual objects:
SQL> begin
dbms_scheduler.enable(
‘BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1’);
end;
SQL> /
There are a couple of special cases that should be noted about enabling group objects:
Scheduler object, regardless of the value of FORCE parameter.
SQL> begin
dbms_scheduler.disable(‘BACKUP_JOB’)
end;
SQL> /
To disable multiple objects, a comma-seperated list can be passed in.
SQL> begin
dbms_scheduler.disable(‘BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB’, TRUE);
end;
SQL> /
The list of objects can also contain both groups and individual objects:
SQL> begin
dbms_scheduler.disable(
‘BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1’);
end;
SQL> /
There are a couple of special cases that should be noted:
DBMS_SCHEDULER does not have an ALTER procedure of any kind. This is because Scheduler objects are collections of attributes. To make a change to an object requires setting its attributes. Therefore, to alter a Scheduler object, DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL procedures are used.
SET_ATTRIBUTE procedure sets an attribute for any type of Scheduler object. The SET_ATTRIBUTE_NULL procedure, on the other hand, sets any attribute to NULL for any type of Scheduler object. This is useful for “unsetting” an attribute.
NOTE: The only attribute that cannot be altered is the name of the object.
When the attributes on an object are changed, Oracle will attempt to disable the object before making the changes. When the attribute has been successfully altered, Oracle will re-enable the object automatically. If the SET_ATTRIBUTE failed, the object will remain disabled (and an error message is returned)
SET_ATTRIBUTE accepts 3 parameters:
NAME: The name of Scheduler object
ATTRIBUTE: The name of attribute to be changed
VALUE: The new value for the attribute.
SET_ATTRIBUTE_NULL accepts 3 parameters:
NAME: The name of Scheduler object
ATTRIBUTE: The name of attribute, which should be set to NULL.
You can enable or disable an object by using the SET_ATTRIBUTE procedure,
SQL> begin
dbms_scheduler.set_attribute (
name => ‘TEST_JOB’,
attribute => ‘ENABLED’,
value => TRUE);
end;
SQL> /
To remove the end date from the schedule, SET_ATTRIBUTE_NULL procedure can be used,
SQL> begin
dbms_scheduler.set_attibute_null (
name => ‘TEST_SCHEDULE’,
attribute => ‘END_DATE’);
end;
SQL> /
Using Scheduler Jobs
A Scheduler jobs defines a specific program to be executed, the arguments (or parameters) to be passed to the program, and the schedule defining when the program should be executed. It also specifies other characteristics such as logging options, job priority, and so on.
Many of these characteristics are explicitly set at job creation time through CREATE_JOB procedure. However, others are inherited from the job class to which the job is assigned. If a job is not explicitly assigned to a job class, these characteristics will be inherited from a job class named DEFAULT_JOB_CLASS.
Creating Jobs
Scheduler jobs can be created using DBMS_SCHEDULER.CREATE_JOB procedure. A job combines a program and a schedule for execution of that program. Therefore, these are the elements that you must define when creating a new job.
Depending on the program that the job uses, you may also need to set job arguments. These are the parameters that will be passed to the program at execution time. Job arguments can be set by using SET_JOB_ARGUMENT and/or SET_JOB_ANYDATA_VALUE procedures in the DBMS_SCHEDULER package.
Jobs also have job attributes that control certain behaviors of the job. Many of these can be set through the CREATE_JOB procedure, while others are inherited from the job class to which the job is assigned (or from the DEFAULT_JOB_CLASS class). For example, job attributes such as JOB_TYPE, JOB_ACTION, and REPEAT_INTERVAL can be defined at job creation time. Other attributes such as MAX_FAILURES, LOGGING_LEVEL, and JOB_PRIORITY are inherited from the job class.
A job is stored like any other database object, so it is vital that a valid object name is used when creating jobs. The job name must also be unique within the schema in which it is created. Like other database objects, jobs can be created in a different schema by prefixing the job name with a schema name. Ex: specifying a job name of BUTTERTB.STATS_JOB would cause the job to be created in the BUTTERTB schema.
Job Attributes
Scheduler jobs have a specific set of attributes that you can set to define the characteristics of the job. These attributes can be set at job creation time through the following CREATE_JOB procedure parameters:
JOB_NAME: The JOB_NAME parameter specifies the name assigned to the new job. Job name must be a valid Oracle object name, and also it must be unique within the schema.
JOB_TYPE: Specifies the type of job that will be created. This is a required parameter and cannot be excluded. It can be any one of the following:
o PLSQL_BLOCK: The job will execute an anonymous PL/SQL block. Anonymous PL/SQL block jobs do not accept job or program arguments, so the number of arguments must be set to 0.
o STORED_PROCEDURE: The job will execute a PL/SQL stored procedure.
o EXECUTABLE: The job will execute a program that is external to the database. An external job is any program that can be executed from the operating system’s command line.
JOB_ACTION: Specifies the code to be executed for this job. It can be a complete PL/SQL block, or one or more lines of valid PL/SQL code. For an executable, the value is the name of the executable including the full path name and applicable command-line arguments. For ex: ‘/prod/bin/big_load.sh full’ would execute big_load.sh script and pass in one argument with the value of full.
NUMBER_OF_ARGUMENTS: Specifies the number of arguments that the job accepts. The range is 0 (default) to 255.
PROGRAM_NAME: Specifies the name of the program associated with the job. The program name must be the name of an existing program object.
START_DATE: Specifies the first date that the job should be run. If both START_DATE and
REPEAT_INTERVAL parameters are NULL, the job will run as soon as it is enabled
NOTE: The Scheduler cannot guarantee that a job will execute at an exact time, because the system may be overloaded and thus resources may be unavailable.
REPEAT_INTERVAL: Specifies how often the job should be repeated. This parameter can be specified using either a calendaring system or a PL/SQL expression. If this parameter is NULL, the job will run only once (at scheduled start time)
SCHEDULE_NAME: Specifies the name of the schedule associated with this job. It can optionally specify a window or window group associated with the job.
END_DATE: Specifies the date when the job will expire. After the date specified, the job will no longer be executed. The state of the job will be set to COMPLETED, and the ENABLED flag is set to FALSE.
If this parameter is set to NULL, then the job will repeat forever. However, if MAX_RUNS or MAX_FAILURES parameters are set, the job will stop if either of these thresholds is met.
JOB_CLASS: Specifies the class to which the job is assigned. If this parameter is NULL, the job is assigned to the default class.
COMMENTS: Allows the entry of a comment to document the job.
ENABLED: Specifies whether the job is created in an enabled state. By default, all jobs are created disabled. A disabled job will exist as an object in the database, but it will never be processed by the job coordinator.
AUTO_DROP: Specifies whether the job will be automatically dropped once it has been executed (for non-repeating jobs) or when the status is changed to COMPLETED (for repeating jobs)
The default for this parameter is TRUE, meaning the job will be dropped
from their database. If it is set to FALSE, the jobs are not dropped and their metadata is retained in the database until it is explicitly dropped by using DBMS_SCHEDULER.DROP_JOB procedure.
CREATE_JOB Procedure Options
By overloading the CREATE_JOB procedure, the Scheduler allows four distinct methods in which jobs can be created:
Method 1 allows the use of inline program and inline schedule definitions. This means that the program and schedule for the job are defined strictly within the confines of CREATE_JOB procedure. They do not exist as independent Scheduler program or scheduler objects.
Method 2 allows the creation of job using an existing program object and schedule object. This method shows how the component nature of the Scheduler can be leveraged through object reuse. Rather than recreate an identical schedule or program, an existing one can be utilized instead.
Method 3 combines an existing program with an inline schedule definition
Method 4 combines an inline program definition with an existing schedule
Using CREATE_JOB Procedure
Ex:
SQL> begin
dbms_scheduler.create_job (
job_name => ‘cola_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘update employee set salary = salary*1.05;’,
start_date => ’10-OCT-2004 06:00:00 AM’,
repeat_interval => ‘FREQ=YEARLY’,
comments => ‘Sample comment’);
end;
SQL> /
To verify that the job was created, the DBA|ALL|USER_SCHEDULER_JOBS view can be queried.
Copying Jobs
Jobs can be copied by using the DBMS_SCHEDULER.COPY_JOB procedure. This procedure accepts only two parameters: OLD_JOB and NEW_JOB. These parameters represent the name of the source and destination job names, respectively.
A copied job will be identical to the original job, with the following exceptions:
The new job will have a different name
The new job will be created in a disabled state
SQL> begin
dbms_scheduler.copy_job(‘COLA_JOB’, ‘RAISE_JOB’);
end;
SQL> /
SQL> select job_name, enabled from user_scheduler_jobs;
Running Jobs
The Scheduler allows scheduled jobs to be run outside of their normal schedule through the DBMS_SCHEDULER.RUN_JOB procedure. This procedure is useful for testing a newly created job or for re-executing a job that failed previously.
The RUN_JOB procedure accepts the JOB_NAME and USE_CURRENT_SESSION parameters. The USE_CURRENT_SESSION is a Boolean value. If the parameter is set to FALSE (default), the job will be submitted to the job scheduler for normal asynchronous execution. If it set to TRUE, the job will be executed in synchronous fashion using the current user session.
SQL> begin
dbms_scheduler.run_job(‘COLA_JOB’, TRUE);
end;
SQL> /
NOTE: Note that only an enabled job may be run using the RUN_JOB procedure
Stopping Jobs
A running job can be stopped using DBMS_SCHEDULER.STOP_JOB procedure. Optionally, a user with MANAGE_SCHEDULER privilege can set the FORCE parameter to TRUE. This causes Oracle to terminate the process running the job and stops the job much faster.
Dropping Jobs
Jobs can be dropped using DBMS_SCHEDULER.DROP_JOB procedure. This procedure removes the job object completely from the database. If an instance of job is running when you issue this procedure, an error will result. If you set the FORCE option to TRUE, Oracle will issue an implicit STOP_JOB procedure to kill the current instance and then drop the job.
SQL> begin
dbms_scheduler.drop_job (job_name => ‘COLA_JOB’, force => TRUE);
end;
SQL> /
Using Job Classes
A job class is a container object for the logical grouping of jobs into a larger unit. Advantages include:
Easier to manage a small number of job groups rather than a large number of individual jobs. Certain job characteristics can be assigned at the group level and will be inherited by all jobs within the group.
Job classes can be assigned to a resource consumer group. This allows you to control resource allocation for all jobs within the group
Jobs can be prioritized within the job class. This gives you more control over which jobs should take precedence in case of a conflict.
All jobs must belong to exactly one job class. Any job not explicitly assigned to a job class will belong to the DEFAULT_JOB_CLASS class, and then will inherit the characteristics of that job class.
Job Class Parameters
Job classes have a specific set of attributes that you can set to define the characteristics of the class. These attributes will be inherited by all jobs assigned to the job class, thereby saving you the work of setting them individually on each job. Available parameters are described here:
JOB_CLASS_NAME Uniquely identifies the job class in the SYS schema.
RESOURCE_CONSUMER_GROUP Associates the job group with a specific consumer group. All jobs assigned to the job group will automatically be governed by this consumer group.
SERVICE Specifies the service to which the job class belongs. In a RAC environment, the jobs in the class will have affinity to the particular service specified. Therefore, they will run only on those database instances that are assigned to the specific service. If this attribute is not set, default service will be used, meaning that the jobs have no service affinity and can be run by any instance within the cluster. If the SERVICE parameter is specified, the RESOURCE_CONSUMER_GROUP attribute cannot be set. They are mutually exclusive.
LOGGING_LEVEL The Oracle Scheduler can optionally maintain job logs of all job activities. Job logging is determined by the setting of LOGGING_LEVEL of job class. This parameter specifies how much job information is logged. There are 3 valid settings for this parameter:
o DBMS_SCHEDULER.LOGGING_OFF No logging will be performed for any jobs in the class
o DBMS_SCHEDULER.LOGGING_RUNS Detailed information will be written for all runs of each job in the class
o DBMS_SCHEDULER.LOGGING_FULL Detailed information will be written for all runs in the class, and every operation performed on any job in the class will be logged
Note that the valid values for this parameter are all constants defined within the DBMS_SCHEDULER package.
LOG_HISTORY This parameter determines the number of days logged information should be retained. Default value is 30 days. Valid values are 1 to 999. When records are exceeded this age, the Scheduler will automatically purge them.
COMMENTS Specifies an optional comment about the job class.
Creating Job Classes
Job classes can be created through DBMS_SCHEDULER.CREATE_JOB_CLASS procedure.
SQL> begin
dbms_scheduler.create_job_class (
job_class_name => ‘LOW_PRIORITY_CLASS’,
resource_consumer_group => ‘LOW_GROUP’,
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
logging_history => 60,
comments => ‘LOW PRIORITY JOB CLASS’);
end;
SQL> /
In the above example, a job class named LOW_PRIORITY_CLASS is created that will assign all jobs in the group to the LOW_GROUP consumer group.
Dropping Job Classes
Job classes can be dropped by using DBMS_SCHEDULER.DROP_JOB_CLASS procedure. Dropping job class that has jobs assigned to it will result in an error. However, it is allowed if the FORCE parameter is set to TRUE. In this case, the job class will be dropped and the jobs assigned to the class will be disabled.
Several job classes can also be dropped by separating the names of job classes by a comma:
SQL> begin
dbms_scheduler.drop_job_class (
‘LOW_PRIORITY_CLASS, HIGH_PRIORITY_CLASS’);
end;
SQL> /
Using Scheduler Programs
A program will define the action that will occur when a job runs. It can be a PL/SQL block, a stored procedure, or an OS executable.
Program Attributes
These attributes can be set at creation time through the following CREATE_PROGRAM procedure parameters:
PROGRAM_NAME Specifies the name assigned to the new program. It must be unique name within the schema
PROGRAM_TYPE Specifies the type of program that will be created. This is a required parameter and cannot be excluded. It can be any one of the following:
o PLSQL_BLOCK The program is an anonymous PL/SQL block
o STORED_PROCEDURE
o EXECUTABLE The program is external to the database. An external program is any program that can be executed from the OS command line.
PROGRAM_ACTION Specifies the code to be executed. It can be a complete PL/SQL code or one or more lines of valid PL/SQL code.
NUMBER_OF_ARGUMENTS Specifies the number of arguments that the job accepts. The range is 0 (default) to 255.
ENABLED Specifies whether the job is created in an enabled state. By default, all programs are created disabled, so default value is FALSE.
COMMENTS Comment to document the program
Creating Programs
Programs, like Jobs are stored as independent schema objects. Therefore, they must have unique names within the schema.
SQL> begin
dbms_scheduler.create_program (
program_name => ‘STATS_PROGRAM’,
program_type => ‘STORED_PROCEDURE’,
program_action => ‘DBMS_STATS.GATHER_SCHEMA_STATS’,
number_of_arguments => 1,
comments => ‘sample comment’);
end;
SQL> /
Argument can be defined using DEFINE_PROGRAM_ARGUMENT procedure,
SQL> begin
dbms_scheduler.define_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_position => 1,
argument_type => ‘VARCHAR2’);
end;
SQL> /
Arguments can be dropped from programs as well. This procedure allows arguments to be dropped by either name or by the position of the argument.
SQL> begin
dbms_scheduler.drop_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_position => 1,
end;
SQL> /
Or its name:
SQL> begin
dbms_scheduler.drop_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_name => ‘SCHEMA_NAME’,
end;
SQL> /
Dropping Programs
This procedure removes the procedure entirely from the database.
SQL> begin
dbms_scheduler.drop_program (
program_name => ‘STATS_PROGRAM’,
force => TRUE
end;
SQL> /
Using Schedules
Schedules define when the jobs run as well as when the windows are opened. Like jobs and programs, schedules are stored objects and follow all the same naming requirements. By saving schedules as independent objects, they can be used by multiple jobs.
Schedule Attributes
These attributes can be set at creation time through CREATE_SCHEDULE procedure:
SCHEDULE_NAME Specifies the name of the schedule. Must be unique within the schema
START_DATE Specifies the first date that the schedule is valid.
END_DATE Specifies the date when the schedule will expire. After the date specified, the job will no longer be executed; state of job will be set to COMPLETED and the ENABLED flag is set to FALSE.
If this parameter is set to NULL, the job will repeat forever. However, if
the MAX_RUNS or MAX_FAILURES parameter is set, the job will stop if either of these thresholds is met.
REPEAT_INTERVAL Specifies how often the schedule should be repeated. If this parameter is NULL, the job will run only once (at scheduled start time)
COMMENTS Allows an entry of a comment to document the schedule
Creating Schedules
Use DBMS_SCHEDULER.CREATE_SCHEDULE procedure.
Ex: A schedule that repeats every night 8:00 PM
SQL> begin
dbms_scheduler.create_schedule (
schedule_name => ‘NIGHTLY_SCHEDULE’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=20’,
comments => ‘Runs nightly at 8:00 PM’);
end;
SQL> /
Setting Repeat Intervals
Oracle’s new calendaring syntax offers tremendous flexibility when it comes to defining repeat intervals.
Calendaring Syntax Element Descriptions
FREQ Defines frequency type. This parameter is required. The following values are valid: YEARLY, MINTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY
INTERVAL an INTERVAL of 1 (default) indicates that the job will be executed every day. A value of 2 means that the job would execute every day. Max value is 999.
BYMONTH Specifies the month in which you want the job to execute. Months can be represented numerically (1-12) or using three-letter abbreviations (JAN-DEC). Multiples months should be separated by commas.
BYWEEKNO Specifies the week of the year as a number. Defines the week as starting with Monday and ending with Sunday.
BYYEARDAY Specifies the day of the year as a number. Negative numbers can be used to count backward.
BYMONTHDAY Specifies the day of the month as a number. Negative numbers can be used to count backward.
BYDAY Specifies the day of the week using a three-letter abbreviation (MON, TUE, and, so on). You can also prepend the BYDAY element with a number representing the occurrence of the specified day. For example, if FREQ is set to MONTHLY, you can specify the last Friday of the month by using -1FRI
BYHOUR Specifies the hour on which the job is run. Valid values are 0 to 23
BYMINUTE Specifies the minute on which the job is to run. Valid values are 0-59
BYSECOND Specifies the second on which the job is to run. Valid values are 0-59
Keep in mind that there are certain rules when using calendaring system:
The first element defined must always be the frequency. All other elements are optional and can appear in any order.
Elements should be separated by a semi-colon, and each element can be represented no more than once.
Lists of values within an element should be separated by commas. They do not need to be ordered.
Calendaring statements are case-insensitive, and white space is allowed between elements.
The BYWEEKNO can be used only when the FREQ is set to YEARLY.
Negative numbers are allowed with certain BY elements. For example months have different number of days, so defining the last day of every month is not possible by using a single, positive number. Instead, you can specify BYMONTHDAY= -1, which always return the last day of the month. Fixed-size elements such as BYMONTH, BYHOUR, do not support negative numbers.
The BYDAY element generally specifies the day of the week. However, when used in conjunction with a frequency of YEARLY or MONTHLY, you can add a positive or negative number in front of the day to achieve greater specificity. For example, a FREQ value set to MONTHLY and BDAY value set to -1SAT would specify the last Saturday of every month.
The calendaring system always considers Monday the first day of the week.
The calendaring system does not allow you to specify time zones or Daylight Savings Time adjustments.
Some examples:
GOAL EXPRESSION
Every Monday FREQ=WEEKLY; BYDAY=MON;
Every other Monday FREQ=WEEKLY; BYDAY=MON; INTERVAL=2;
Last day of each month FREQ=MONTHLY; BYMONTHDAY=-1;
Every January 7 FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=7;
Second Wednesday of each month FREQ=MONTHLY; BYDAY=2WED;
Every hour FREQ=HOURLY;
Every 4 hours FREQ=HOURLY; INTERVAL=4;
Hourly on first day of each month FREQ=HOURLY; BYMONTHDAY=1;
15th day of every other month FREQ=MONTHLY; BYMONTHDAY=15; INTERVAL=2
Testing Repeat Intervals
Oracle offers DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure. This procedure allows you to pass in a calendaring syntax expression and a start date, and it will return the time and date that the job will execute next.
EVALUATE_CALENDAR_STRING Parameters
CALENDAR_STRING The calendar expression to be evaluated
START_DATE Date after which the repeat interval becomes valid
RETURN_DATE_AFTER Instructs procedure to return only execution dates that will
occur after the date specified in this parameter.
NEXT_RUN_DATE This is an out parameter of type TIMESTAMP that shows the
date and time of the next execution.
Ex:
SQL> declare
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
begin
start_date := to_timestamp_tz (
’10-OCT-2004 10:00:00’, ‘DD-MON-YYYY HH24:MI:SS’);
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
‘FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15’;
start_date, null, next_run_date);
dbms_output.put_line(‘next_run_date: ‘ || next_run_date);
end;
SQL> /
Using Scheduler Windows
A scheduler window allows you to change the active resource plan based on defined schedules. For instance, assume that your system performs heavy transaction processing between the hours of 8:00 am and 5:00 pm., but runs mostly batch processing and reports after hours. It would make sense to create a separate resource plans to govern resource allocation for each time period. Scheduler windows can then be used to switch automatically between the two.
Unlike other scheduler objects that you have seen so far, windows are created in the SYS schema. They are stored as database objects and therefore must have a valid name that is unique within in SYS schema.
Creating Windows
Windows can be created using DBMS_SCHEDULER.CREATE_WINDOW procedure. When creating a window, you have the choice of either using an existing schedule or defining an inline schedule.
WINDOW_NAME Uniquely identifies the window in the SYS schema. The name has to be unique in the SYS schema
RESOURCE_PLAN Specifies the resource plan that will govern the timeframe of the window. When the window opens, the system switches to the specified resource plan. If the current resource plan has been set through the use of ALTER SYSTEM SET RESOURCE_MANAGER_PLAN FORCE statement, the Scheduler will not be allowed to change the resource plan. If no resource plan is defined for the window, the current resource plan will remain effect when the window opens and will stay in effect for the duration of the window.
START_DATE Specifies the first date that the window is scheduled to open. If START_DATE is NULL, the window will open as soon as it is created.
DURATION Specifies how long the window will remain open. There is no default value, so a value must be provided. The value should be provided as an INTERVAL DAY TO SECOND datatype (for example, interval ‘10’ hour or interval ‘20’ minute).
SCHEDULE_NAME Specifies the name of the schedule associated with the window.
REPEAT_INTERVAL Specifies how often the window should be repeated. It is defined using calendaring syntax only. If set to NULL, the window will open only once at the specified start date.
END_DATE Specifies the date when the window will be disabled. If set to NULL, a repeating window will repeat forever.
WINDOW_PRIORITY Is relevant only when two windows overlap each other. The valid values are LOW (default) and HIGH. A high priority window has precedence.
COMMENTS Specifies an optional comment about the window.
To create a window that activates the DAY_PLAN resource plan and uses a schedule named WORK_HOURS_SCHEDULE,
SQL> begin
dbms_scheduler.create_window (
window_name => ‘WORK_HOURS_WINDOW’,
resource_plan => ‘DAY_PLAN’,
schedule_name => ‘WORK_HOURS_SCHEDULE’,
duration => INTERVAL ‘10’ HOUR,
window_priority => ‘HIGH’);
end;
SQL> /
Opening and Closing Windows
SQL> begin
dbms_scheduler.open_window (
window_name => ‘WORK_HOURS_WINDOW’,
duration => INTERVAL ‘20’ MINUTE,
force => TRUE);
end;
SQL> /
If the FORCE parameter is set to TRUE in the OPEN_WINDOW procedure, the Scheduler will automatically close any currently open window, even if it has a higher priority. Also, it will not allow any other windows to be opened for the duration of the manually opened window.
SQL> begin
dbms_scheduler.close_window (
window_name => ‘WORK_HOURS_WINDOW’);
end;
SQL> /
SQL> select owner, job_name, state from dba_scheduler_jobs;
To enable an object, use DBMS_SCHEDULER.ENABLE procedure. The procedure accepts only one argument, NAME, which designates one of the following:
The name of specific object
A comma-seperated list of objects
SQL> begin
dbms_scheduler.enable(‘BACKUP_JOB’);
end;
SQL> /
To enable multiple objects, comma-seperated list can be passed in. Note that the entire list is enclosed in single quotes.
SQL> begin
dbms_scheduler.enable(‘BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB’);
end;
SQL> /
The list of objects can also contain both groups and individual objects:
SQL> begin
dbms_scheduler.enable(
‘BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1’);
end;
SQL> /
There are a couple of special cases that should be noted about enabling group objects:
- When a job group is enabled, all members of that group will be enabled
- When a window group is enabled, only window group object is enabled. Windows that are members of the group are not enabled.
- When a window or a window group is referenced in the ENABLE procedure, it must always be prefixed with the SYS schema name (SYS.WINDOW_GROUP_1).
Scheduler object, regardless of the value of FORCE parameter.
SQL> begin
dbms_scheduler.disable(‘BACKUP_JOB’)
end;
SQL> /
To disable multiple objects, a comma-seperated list can be passed in.
SQL> begin
dbms_scheduler.disable(‘BACKUP_PROGRAM, BACKUP_JOB, STATS_JOB’, TRUE);
end;
SQL> /
The list of objects can also contain both groups and individual objects:
SQL> begin
dbms_scheduler.disable(
‘BACKUP_JOB_GROUP, STATS_JOB, SYS.WINDOW_GROUP_1’);
end;
SQL> /
There are a couple of special cases that should be noted:
- Disabling a window group does not disable jobs that reference the group. However, those jobs will fail when they try to execute
- Disabling a window group does not affect members of the group. They will continue to function normally.
DBMS_SCHEDULER does not have an ALTER procedure of any kind. This is because Scheduler objects are collections of attributes. To make a change to an object requires setting its attributes. Therefore, to alter a Scheduler object, DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL procedures are used.
SET_ATTRIBUTE procedure sets an attribute for any type of Scheduler object. The SET_ATTRIBUTE_NULL procedure, on the other hand, sets any attribute to NULL for any type of Scheduler object. This is useful for “unsetting” an attribute.
NOTE: The only attribute that cannot be altered is the name of the object.
When the attributes on an object are changed, Oracle will attempt to disable the object before making the changes. When the attribute has been successfully altered, Oracle will re-enable the object automatically. If the SET_ATTRIBUTE failed, the object will remain disabled (and an error message is returned)
SET_ATTRIBUTE accepts 3 parameters:
NAME: The name of Scheduler object
ATTRIBUTE: The name of attribute to be changed
VALUE: The new value for the attribute.
SET_ATTRIBUTE_NULL accepts 3 parameters:
NAME: The name of Scheduler object
ATTRIBUTE: The name of attribute, which should be set to NULL.
You can enable or disable an object by using the SET_ATTRIBUTE procedure,
SQL> begin
dbms_scheduler.set_attribute (
name => ‘TEST_JOB’,
attribute => ‘ENABLED’,
value => TRUE);
end;
SQL> /
To remove the end date from the schedule, SET_ATTRIBUTE_NULL procedure can be used,
SQL> begin
dbms_scheduler.set_attibute_null (
name => ‘TEST_SCHEDULE’,
attribute => ‘END_DATE’);
end;
SQL> /
Using Scheduler Jobs
A Scheduler jobs defines a specific program to be executed, the arguments (or parameters) to be passed to the program, and the schedule defining when the program should be executed. It also specifies other characteristics such as logging options, job priority, and so on.
Many of these characteristics are explicitly set at job creation time through CREATE_JOB procedure. However, others are inherited from the job class to which the job is assigned. If a job is not explicitly assigned to a job class, these characteristics will be inherited from a job class named DEFAULT_JOB_CLASS.
Creating Jobs
Scheduler jobs can be created using DBMS_SCHEDULER.CREATE_JOB procedure. A job combines a program and a schedule for execution of that program. Therefore, these are the elements that you must define when creating a new job.
Depending on the program that the job uses, you may also need to set job arguments. These are the parameters that will be passed to the program at execution time. Job arguments can be set by using SET_JOB_ARGUMENT and/or SET_JOB_ANYDATA_VALUE procedures in the DBMS_SCHEDULER package.
Jobs also have job attributes that control certain behaviors of the job. Many of these can be set through the CREATE_JOB procedure, while others are inherited from the job class to which the job is assigned (or from the DEFAULT_JOB_CLASS class). For example, job attributes such as JOB_TYPE, JOB_ACTION, and REPEAT_INTERVAL can be defined at job creation time. Other attributes such as MAX_FAILURES, LOGGING_LEVEL, and JOB_PRIORITY are inherited from the job class.
A job is stored like any other database object, so it is vital that a valid object name is used when creating jobs. The job name must also be unique within the schema in which it is created. Like other database objects, jobs can be created in a different schema by prefixing the job name with a schema name. Ex: specifying a job name of BUTTERTB.STATS_JOB would cause the job to be created in the BUTTERTB schema.
Job Attributes
Scheduler jobs have a specific set of attributes that you can set to define the characteristics of the job. These attributes can be set at job creation time through the following CREATE_JOB procedure parameters:
JOB_NAME: The JOB_NAME parameter specifies the name assigned to the new job. Job name must be a valid Oracle object name, and also it must be unique within the schema.
JOB_TYPE: Specifies the type of job that will be created. This is a required parameter and cannot be excluded. It can be any one of the following:
o PLSQL_BLOCK: The job will execute an anonymous PL/SQL block. Anonymous PL/SQL block jobs do not accept job or program arguments, so the number of arguments must be set to 0.
o STORED_PROCEDURE: The job will execute a PL/SQL stored procedure.
o EXECUTABLE: The job will execute a program that is external to the database. An external job is any program that can be executed from the operating system’s command line.
JOB_ACTION: Specifies the code to be executed for this job. It can be a complete PL/SQL block, or one or more lines of valid PL/SQL code. For an executable, the value is the name of the executable including the full path name and applicable command-line arguments. For ex: ‘/prod/bin/big_load.sh full’ would execute big_load.sh script and pass in one argument with the value of full.
NUMBER_OF_ARGUMENTS: Specifies the number of arguments that the job accepts. The range is 0 (default) to 255.
PROGRAM_NAME: Specifies the name of the program associated with the job. The program name must be the name of an existing program object.
START_DATE: Specifies the first date that the job should be run. If both START_DATE and
REPEAT_INTERVAL parameters are NULL, the job will run as soon as it is enabled
NOTE: The Scheduler cannot guarantee that a job will execute at an exact time, because the system may be overloaded and thus resources may be unavailable.
REPEAT_INTERVAL: Specifies how often the job should be repeated. This parameter can be specified using either a calendaring system or a PL/SQL expression. If this parameter is NULL, the job will run only once (at scheduled start time)
SCHEDULE_NAME: Specifies the name of the schedule associated with this job. It can optionally specify a window or window group associated with the job.
END_DATE: Specifies the date when the job will expire. After the date specified, the job will no longer be executed. The state of the job will be set to COMPLETED, and the ENABLED flag is set to FALSE.
If this parameter is set to NULL, then the job will repeat forever. However, if MAX_RUNS or MAX_FAILURES parameters are set, the job will stop if either of these thresholds is met.
JOB_CLASS: Specifies the class to which the job is assigned. If this parameter is NULL, the job is assigned to the default class.
COMMENTS: Allows the entry of a comment to document the job.
ENABLED: Specifies whether the job is created in an enabled state. By default, all jobs are created disabled. A disabled job will exist as an object in the database, but it will never be processed by the job coordinator.
AUTO_DROP: Specifies whether the job will be automatically dropped once it has been executed (for non-repeating jobs) or when the status is changed to COMPLETED (for repeating jobs)
The default for this parameter is TRUE, meaning the job will be dropped
from their database. If it is set to FALSE, the jobs are not dropped and their metadata is retained in the database until it is explicitly dropped by using DBMS_SCHEDULER.DROP_JOB procedure.
CREATE_JOB Procedure Options
By overloading the CREATE_JOB procedure, the Scheduler allows four distinct methods in which jobs can be created:
Method 1 allows the use of inline program and inline schedule definitions. This means that the program and schedule for the job are defined strictly within the confines of CREATE_JOB procedure. They do not exist as independent Scheduler program or scheduler objects.
Method 2 allows the creation of job using an existing program object and schedule object. This method shows how the component nature of the Scheduler can be leveraged through object reuse. Rather than recreate an identical schedule or program, an existing one can be utilized instead.
Method 3 combines an existing program with an inline schedule definition
Method 4 combines an inline program definition with an existing schedule
Using CREATE_JOB Procedure
Ex:
SQL> begin
dbms_scheduler.create_job (
job_name => ‘cola_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘update employee set salary = salary*1.05;’,
start_date => ’10-OCT-2004 06:00:00 AM’,
repeat_interval => ‘FREQ=YEARLY’,
comments => ‘Sample comment’);
end;
SQL> /
To verify that the job was created, the DBA|ALL|USER_SCHEDULER_JOBS view can be queried.
Copying Jobs
Jobs can be copied by using the DBMS_SCHEDULER.COPY_JOB procedure. This procedure accepts only two parameters: OLD_JOB and NEW_JOB. These parameters represent the name of the source and destination job names, respectively.
A copied job will be identical to the original job, with the following exceptions:
The new job will have a different name
The new job will be created in a disabled state
SQL> begin
dbms_scheduler.copy_job(‘COLA_JOB’, ‘RAISE_JOB’);
end;
SQL> /
SQL> select job_name, enabled from user_scheduler_jobs;
Running Jobs
The Scheduler allows scheduled jobs to be run outside of their normal schedule through the DBMS_SCHEDULER.RUN_JOB procedure. This procedure is useful for testing a newly created job or for re-executing a job that failed previously.
The RUN_JOB procedure accepts the JOB_NAME and USE_CURRENT_SESSION parameters. The USE_CURRENT_SESSION is a Boolean value. If the parameter is set to FALSE (default), the job will be submitted to the job scheduler for normal asynchronous execution. If it set to TRUE, the job will be executed in synchronous fashion using the current user session.
SQL> begin
dbms_scheduler.run_job(‘COLA_JOB’, TRUE);
end;
SQL> /
NOTE: Note that only an enabled job may be run using the RUN_JOB procedure
Stopping Jobs
A running job can be stopped using DBMS_SCHEDULER.STOP_JOB procedure. Optionally, a user with MANAGE_SCHEDULER privilege can set the FORCE parameter to TRUE. This causes Oracle to terminate the process running the job and stops the job much faster.
Dropping Jobs
Jobs can be dropped using DBMS_SCHEDULER.DROP_JOB procedure. This procedure removes the job object completely from the database. If an instance of job is running when you issue this procedure, an error will result. If you set the FORCE option to TRUE, Oracle will issue an implicit STOP_JOB procedure to kill the current instance and then drop the job.
SQL> begin
dbms_scheduler.drop_job (job_name => ‘COLA_JOB’, force => TRUE);
end;
SQL> /
Using Job Classes
A job class is a container object for the logical grouping of jobs into a larger unit. Advantages include:
Easier to manage a small number of job groups rather than a large number of individual jobs. Certain job characteristics can be assigned at the group level and will be inherited by all jobs within the group.
Job classes can be assigned to a resource consumer group. This allows you to control resource allocation for all jobs within the group
Jobs can be prioritized within the job class. This gives you more control over which jobs should take precedence in case of a conflict.
All jobs must belong to exactly one job class. Any job not explicitly assigned to a job class will belong to the DEFAULT_JOB_CLASS class, and then will inherit the characteristics of that job class.
Job Class Parameters
Job classes have a specific set of attributes that you can set to define the characteristics of the class. These attributes will be inherited by all jobs assigned to the job class, thereby saving you the work of setting them individually on each job. Available parameters are described here:
JOB_CLASS_NAME Uniquely identifies the job class in the SYS schema.
RESOURCE_CONSUMER_GROUP Associates the job group with a specific consumer group. All jobs assigned to the job group will automatically be governed by this consumer group.
SERVICE Specifies the service to which the job class belongs. In a RAC environment, the jobs in the class will have affinity to the particular service specified. Therefore, they will run only on those database instances that are assigned to the specific service. If this attribute is not set, default service will be used, meaning that the jobs have no service affinity and can be run by any instance within the cluster. If the SERVICE parameter is specified, the RESOURCE_CONSUMER_GROUP attribute cannot be set. They are mutually exclusive.
LOGGING_LEVEL The Oracle Scheduler can optionally maintain job logs of all job activities. Job logging is determined by the setting of LOGGING_LEVEL of job class. This parameter specifies how much job information is logged. There are 3 valid settings for this parameter:
o DBMS_SCHEDULER.LOGGING_OFF No logging will be performed for any jobs in the class
o DBMS_SCHEDULER.LOGGING_RUNS Detailed information will be written for all runs of each job in the class
o DBMS_SCHEDULER.LOGGING_FULL Detailed information will be written for all runs in the class, and every operation performed on any job in the class will be logged
Note that the valid values for this parameter are all constants defined within the DBMS_SCHEDULER package.
LOG_HISTORY This parameter determines the number of days logged information should be retained. Default value is 30 days. Valid values are 1 to 999. When records are exceeded this age, the Scheduler will automatically purge them.
COMMENTS Specifies an optional comment about the job class.
Creating Job Classes
Job classes can be created through DBMS_SCHEDULER.CREATE_JOB_CLASS procedure.
SQL> begin
dbms_scheduler.create_job_class (
job_class_name => ‘LOW_PRIORITY_CLASS’,
resource_consumer_group => ‘LOW_GROUP’,
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
logging_history => 60,
comments => ‘LOW PRIORITY JOB CLASS’);
end;
SQL> /
In the above example, a job class named LOW_PRIORITY_CLASS is created that will assign all jobs in the group to the LOW_GROUP consumer group.
Dropping Job Classes
Job classes can be dropped by using DBMS_SCHEDULER.DROP_JOB_CLASS procedure. Dropping job class that has jobs assigned to it will result in an error. However, it is allowed if the FORCE parameter is set to TRUE. In this case, the job class will be dropped and the jobs assigned to the class will be disabled.
Several job classes can also be dropped by separating the names of job classes by a comma:
SQL> begin
dbms_scheduler.drop_job_class (
‘LOW_PRIORITY_CLASS, HIGH_PRIORITY_CLASS’);
end;
SQL> /
Using Scheduler Programs
A program will define the action that will occur when a job runs. It can be a PL/SQL block, a stored procedure, or an OS executable.
Program Attributes
These attributes can be set at creation time through the following CREATE_PROGRAM procedure parameters:
PROGRAM_NAME Specifies the name assigned to the new program. It must be unique name within the schema
PROGRAM_TYPE Specifies the type of program that will be created. This is a required parameter and cannot be excluded. It can be any one of the following:
o PLSQL_BLOCK The program is an anonymous PL/SQL block
o STORED_PROCEDURE
o EXECUTABLE The program is external to the database. An external program is any program that can be executed from the OS command line.
PROGRAM_ACTION Specifies the code to be executed. It can be a complete PL/SQL code or one or more lines of valid PL/SQL code.
NUMBER_OF_ARGUMENTS Specifies the number of arguments that the job accepts. The range is 0 (default) to 255.
ENABLED Specifies whether the job is created in an enabled state. By default, all programs are created disabled, so default value is FALSE.
COMMENTS Comment to document the program
Creating Programs
Programs, like Jobs are stored as independent schema objects. Therefore, they must have unique names within the schema.
SQL> begin
dbms_scheduler.create_program (
program_name => ‘STATS_PROGRAM’,
program_type => ‘STORED_PROCEDURE’,
program_action => ‘DBMS_STATS.GATHER_SCHEMA_STATS’,
number_of_arguments => 1,
comments => ‘sample comment’);
end;
SQL> /
Argument can be defined using DEFINE_PROGRAM_ARGUMENT procedure,
SQL> begin
dbms_scheduler.define_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_position => 1,
argument_type => ‘VARCHAR2’);
end;
SQL> /
Arguments can be dropped from programs as well. This procedure allows arguments to be dropped by either name or by the position of the argument.
SQL> begin
dbms_scheduler.drop_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_position => 1,
end;
SQL> /
Or its name:
SQL> begin
dbms_scheduler.drop_program_argument (
program_name => ‘STATS_PROGRAM’,
argument_name => ‘SCHEMA_NAME’,
end;
SQL> /
Dropping Programs
This procedure removes the procedure entirely from the database.
SQL> begin
dbms_scheduler.drop_program (
program_name => ‘STATS_PROGRAM’,
force => TRUE
end;
SQL> /
Using Schedules
Schedules define when the jobs run as well as when the windows are opened. Like jobs and programs, schedules are stored objects and follow all the same naming requirements. By saving schedules as independent objects, they can be used by multiple jobs.
Schedule Attributes
These attributes can be set at creation time through CREATE_SCHEDULE procedure:
SCHEDULE_NAME Specifies the name of the schedule. Must be unique within the schema
START_DATE Specifies the first date that the schedule is valid.
END_DATE Specifies the date when the schedule will expire. After the date specified, the job will no longer be executed; state of job will be set to COMPLETED and the ENABLED flag is set to FALSE.
If this parameter is set to NULL, the job will repeat forever. However, if
the MAX_RUNS or MAX_FAILURES parameter is set, the job will stop if either of these thresholds is met.
REPEAT_INTERVAL Specifies how often the schedule should be repeated. If this parameter is NULL, the job will run only once (at scheduled start time)
COMMENTS Allows an entry of a comment to document the schedule
Creating Schedules
Use DBMS_SCHEDULER.CREATE_SCHEDULE procedure.
Ex: A schedule that repeats every night 8:00 PM
SQL> begin
dbms_scheduler.create_schedule (
schedule_name => ‘NIGHTLY_SCHEDULE’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=20’,
comments => ‘Runs nightly at 8:00 PM’);
end;
SQL> /
Setting Repeat Intervals
Oracle’s new calendaring syntax offers tremendous flexibility when it comes to defining repeat intervals.
Calendaring Syntax Element Descriptions
FREQ Defines frequency type. This parameter is required. The following values are valid: YEARLY, MINTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY
INTERVAL an INTERVAL of 1 (default) indicates that the job will be executed every day. A value of 2 means that the job would execute every day. Max value is 999.
BYMONTH Specifies the month in which you want the job to execute. Months can be represented numerically (1-12) or using three-letter abbreviations (JAN-DEC). Multiples months should be separated by commas.
BYWEEKNO Specifies the week of the year as a number. Defines the week as starting with Monday and ending with Sunday.
BYYEARDAY Specifies the day of the year as a number. Negative numbers can be used to count backward.
BYMONTHDAY Specifies the day of the month as a number. Negative numbers can be used to count backward.
BYDAY Specifies the day of the week using a three-letter abbreviation (MON, TUE, and, so on). You can also prepend the BYDAY element with a number representing the occurrence of the specified day. For example, if FREQ is set to MONTHLY, you can specify the last Friday of the month by using -1FRI
BYHOUR Specifies the hour on which the job is run. Valid values are 0 to 23
BYMINUTE Specifies the minute on which the job is to run. Valid values are 0-59
BYSECOND Specifies the second on which the job is to run. Valid values are 0-59
Keep in mind that there are certain rules when using calendaring system:
The first element defined must always be the frequency. All other elements are optional and can appear in any order.
Elements should be separated by a semi-colon, and each element can be represented no more than once.
Lists of values within an element should be separated by commas. They do not need to be ordered.
Calendaring statements are case-insensitive, and white space is allowed between elements.
The BYWEEKNO can be used only when the FREQ is set to YEARLY.
Negative numbers are allowed with certain BY elements. For example months have different number of days, so defining the last day of every month is not possible by using a single, positive number. Instead, you can specify BYMONTHDAY= -1, which always return the last day of the month. Fixed-size elements such as BYMONTH, BYHOUR, do not support negative numbers.
The BYDAY element generally specifies the day of the week. However, when used in conjunction with a frequency of YEARLY or MONTHLY, you can add a positive or negative number in front of the day to achieve greater specificity. For example, a FREQ value set to MONTHLY and BDAY value set to -1SAT would specify the last Saturday of every month.
The calendaring system always considers Monday the first day of the week.
The calendaring system does not allow you to specify time zones or Daylight Savings Time adjustments.
Some examples:
GOAL EXPRESSION
Every Monday FREQ=WEEKLY; BYDAY=MON;
Every other Monday FREQ=WEEKLY; BYDAY=MON; INTERVAL=2;
Last day of each month FREQ=MONTHLY; BYMONTHDAY=-1;
Every January 7 FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=7;
Second Wednesday of each month FREQ=MONTHLY; BYDAY=2WED;
Every hour FREQ=HOURLY;
Every 4 hours FREQ=HOURLY; INTERVAL=4;
Hourly on first day of each month FREQ=HOURLY; BYMONTHDAY=1;
15th day of every other month FREQ=MONTHLY; BYMONTHDAY=15; INTERVAL=2
Testing Repeat Intervals
Oracle offers DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure. This procedure allows you to pass in a calendaring syntax expression and a start date, and it will return the time and date that the job will execute next.
EVALUATE_CALENDAR_STRING Parameters
CALENDAR_STRING The calendar expression to be evaluated
START_DATE Date after which the repeat interval becomes valid
RETURN_DATE_AFTER Instructs procedure to return only execution dates that will
occur after the date specified in this parameter.
NEXT_RUN_DATE This is an out parameter of type TIMESTAMP that shows the
date and time of the next execution.
Ex:
SQL> declare
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
begin
start_date := to_timestamp_tz (
’10-OCT-2004 10:00:00’, ‘DD-MON-YYYY HH24:MI:SS’);
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
‘FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15’;
start_date, null, next_run_date);
dbms_output.put_line(‘next_run_date: ‘ || next_run_date);
end;
SQL> /
Using Scheduler Windows
A scheduler window allows you to change the active resource plan based on defined schedules. For instance, assume that your system performs heavy transaction processing between the hours of 8:00 am and 5:00 pm., but runs mostly batch processing and reports after hours. It would make sense to create a separate resource plans to govern resource allocation for each time period. Scheduler windows can then be used to switch automatically between the two.
Unlike other scheduler objects that you have seen so far, windows are created in the SYS schema. They are stored as database objects and therefore must have a valid name that is unique within in SYS schema.
Creating Windows
Windows can be created using DBMS_SCHEDULER.CREATE_WINDOW procedure. When creating a window, you have the choice of either using an existing schedule or defining an inline schedule.
WINDOW_NAME Uniquely identifies the window in the SYS schema. The name has to be unique in the SYS schema
RESOURCE_PLAN Specifies the resource plan that will govern the timeframe of the window. When the window opens, the system switches to the specified resource plan. If the current resource plan has been set through the use of ALTER SYSTEM SET RESOURCE_MANAGER_PLAN FORCE statement, the Scheduler will not be allowed to change the resource plan. If no resource plan is defined for the window, the current resource plan will remain effect when the window opens and will stay in effect for the duration of the window.
START_DATE Specifies the first date that the window is scheduled to open. If START_DATE is NULL, the window will open as soon as it is created.
DURATION Specifies how long the window will remain open. There is no default value, so a value must be provided. The value should be provided as an INTERVAL DAY TO SECOND datatype (for example, interval ‘10’ hour or interval ‘20’ minute).
SCHEDULE_NAME Specifies the name of the schedule associated with the window.
REPEAT_INTERVAL Specifies how often the window should be repeated. It is defined using calendaring syntax only. If set to NULL, the window will open only once at the specified start date.
END_DATE Specifies the date when the window will be disabled. If set to NULL, a repeating window will repeat forever.
WINDOW_PRIORITY Is relevant only when two windows overlap each other. The valid values are LOW (default) and HIGH. A high priority window has precedence.
COMMENTS Specifies an optional comment about the window.
To create a window that activates the DAY_PLAN resource plan and uses a schedule named WORK_HOURS_SCHEDULE,
SQL> begin
dbms_scheduler.create_window (
window_name => ‘WORK_HOURS_WINDOW’,
resource_plan => ‘DAY_PLAN’,
schedule_name => ‘WORK_HOURS_SCHEDULE’,
duration => INTERVAL ‘10’ HOUR,
window_priority => ‘HIGH’);
end;
SQL> /
Opening and Closing Windows
SQL> begin
dbms_scheduler.open_window (
window_name => ‘WORK_HOURS_WINDOW’,
duration => INTERVAL ‘20’ MINUTE,
force => TRUE);
end;
SQL> /
If the FORCE parameter is set to TRUE in the OPEN_WINDOW procedure, the Scheduler will automatically close any currently open window, even if it has a higher priority. Also, it will not allow any other windows to be opened for the duration of the manually opened window.
SQL> begin
dbms_scheduler.close_window (
window_name => ‘WORK_HOURS_WINDOW’);
end;
SQL> /
Subscribe to:
Posts (Atom)