Quartz on Oracle Database

Status: official. The JDBC job store, the OracleDelegate and the two DDL scripts are part of Quartz itself and are maintained with it, unlike the MongoDB and Redis stores. Quartz’s own tutorial lists Oracle among the databases the JDBC job store "has been used widely with".

Source: Spring Boot: Quartz Scheduler and Testcontainers: Oracle Free Module. mvn dependency:tree showed ojdbc11 23.26.3.0.0 and Quartz 2.5.2 for this list; the artifact page of the driver is ojdbc11 on Maven Central.

Two details of that list matter. The driver version is not an incidental choice on Oracle: it decides which schema script works, so check it with mvn dependency:tree instead of assuming (Boot 4.1.1 manages 23.26.3.0.0, Boot 3.5.16 manages 23.7.0.25.01). And the Testcontainers 2.x artifact is testcontainers-oracle-free with the container class in the package org.testcontainers.oracle; the 2.0.5 jar contains only that package, no deprecated alias in org.testcontainers.containers.

To pin the driver to an older release, override the Boot property in the POM; this is how the ojdbc11 21.9.0.0 runs on this page were made:

Source: Spring Boot: Dependency Versions. The same override was applied as -Doracle-database.version=21.9.0.0 on the Maven command line for the measurements.

org.quartz.impl.jdbcjobstore.oracle.OracleDelegate lives in an oracle sub-package, which is the most common way to misspell it: there is no org.quartz.impl.jdbcjobstore.OracleDelegate class in the jar, and a driverDelegateClass without the sub-package fails with a class-not-found error at startup. Its Javadoc calls it "a driver delegate for the Oracle 10 and 11 database". It extends StdJDBCDelegate and overrides the code that writes and reads BLOB columns, nothing else:

Source: OracleDelegate.java (v2.5.2) and Quartz Javadoc: OracleDelegate.

The class also depends on Oracle’s own oracle.sql.BLOB, so it needs the Oracle driver on the classpath to load, and it is the reason the delegate is tied to Oracle’s driver and not to any wrapper that hands out another Blob type. The sibling oracle.weblogic.WebLogicOracleDelegate exists for Oracle drivers used inside a WebLogic server; the Quartz configuration page lists it as org.quartz.impl.jdbcjobstore.oracle.WebLogicOracleDelegate, which is not in the jar (the class is in the oracle.weblogic sub-package).

What matters in practice is that the extra machinery is no longer needed with a current driver. The Bookshelf jobs were run twice on Oracle Database Free with ojdbc11 23.26.3.0.0, once with no driverDelegateClass (the scheduler then reports no delegate class and Quartz uses StdJDBCDelegate) and once with OracleDelegate, and a second test stored data of hundreds of kilobytes:

Source: Quartz documentation: Configure JDBCJobStoreTX. The scenario is a test run (store, read back, compare) against a schema created from tables_oracle23.sql; the last row is an Oracle fact, not a Quartz one: Oracle stores an empty string as NULL.

The recommendation that follows is conservative. Leaving the delegate unset is what was tested most (it is also what Boot does when you set nothing), and OracleDelegate is accepted and worked in every run where the schema was right. Choose it if you want the vendor-specific BLOB path that the Quartz documentation names for Oracle, and keep an eye on it when you upgrade the driver, because it casts to a class (oracle.sql.BLOB) that is marked deprecated in the driver; leaving the delegate unset removes that dependency. The setting for the OracleDelegate through Spring Boot is the spring.quartz.properties key shown in application.yml.

The 2.5.2 jar carries two Oracle scripts under org/quartz/impl/jdbcjobstore/, and the 2.3.2 and 2.4.1 jars carry only the first one (read from the jar listings). They differ in one thing, the type of the boolean flags:

Source: tables_oracle23.sql (v2.5.2) and tables_oracle.sql (v2.5.2); the table is the output of diff on the two files from the jar, plus a trailing blank line that only the old script has.

Everything else is shared: eleven CREATE TABLE statements and twenty CREATE INDEX statements (counted in the file and confirmed with user_tables and user_indexes after a run), VARCHAR2 names of 120 characters for the scheduler name and 200 for the job and trigger names, NUMBER(13) columns for every time (epoch milliseconds, so the database time zone never matters), BLOB columns for the job data, the trigger data, the calendars and the blob triggers, and unquoted lower-case identifiers, which Oracle folds to upper case. The script’s comment says Oracle "MUST be created as shared" with job_queue_processes above 2 and then immediately says other users have run in dedicated mode; the hint dates from the first Quartz releases and it is not needed for the JDBC job store as measured here (the Free image ran in its default mode).

Every Oracle script starts with eleven delete from qrtz_…​ statements and eleven drop table qrtz_…​ statements, with no IF EXISTS, before the first CREATE TABLE. On a schema that has no Quartz tables yet each of them fails with ORA-00942 (table or view does not exist). That has three consequences, each measured:

A migration tool must not receive those lines: Flyway and Liquibase stop at the first failing statement, and a migration is supposed to run exactly once on an empty schema. The edit is to delete them and keep every CREATE:

Source: tables_oracle23.sql (v2.5.2). The result has 11 CREATE TABLE and 20 CREATE INDEX statements and is byte-identical to the file used in every Flyway, Liquibase and strict (continue-on-error: false) run below.

The same sed on tables_oracle.sql gives the VARCHAR2 variant. Both were run through Boot’s initializer with continue-on-error: false as the schema user QUARTZ described next, and the Bookshelf scenario passed with the 23 script on ojdbc11 23.26.3.0.0, and with the old script on ojdbc11 21.9.0.0 (and failed with ORA-12899 on 23.26.3.0.0, as the matrix says).

Quartz needs a schema of its own, or at least its own set of tables: a user that owns the eleven tables is the simplest arrangement. The Free image is a container database whose application data lives in the pluggable database FREEPDB1, so the connection uses that service name, not the container root. The commands were run as SYSTEM against FREEPDB1:

Source: Oracle Database SQL Language Reference: CREATE USER. Executed through JDBC against jdbc:oracle:thin:@//localhost:<mapped-port>/FREEPDB1 as SYSTEM; a test password that is not shown here was used. Querying session_privs as the new user returned exactly CREATE SESSION and CREATE TABLE.

The user created that way ran the whole Bookshelf scenario: Boot’s initializer created the eleven tables and the twenty indexes in the QUARTZ schema, Quartz fired the jobs and stored a serialised JobDataMap, and Flyway and Liquibase (next sections) used the same user to create their own bookkeeping tables. No privilege was granted on any other schema and no role was needed. The quota is what lets the user store the table, index and BLOB segments; the CREATE TABLE privilege also covers the indexes the owner creates in its own schema. In production, run the creation script as an administrator once, keep the password in a secret store, and give the application only ${DB_PASSWORD} (see application.yml).

The convenient alternative in tests is to let the Testcontainers module create the user: new OracleContainer(image).withUsername("quartz").withPassword("…​") makes the image create an application user with that name, and querying it showed far more than Quartz needs: 29 session privileges (including CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE JOB and CREATE MATERIALIZED VIEW) and the roles DB_DEVELOPER_ROLE, RESOURCE and SODA_APP. That is fine for a throw-away container and wrong as a model for a real database.

Two Oracle facts worth keeping next to the setup. If the Quartz tables must live in another schema than the connecting user’s, Quartz substitutes org.quartz.jobStore.tablePrefix verbatim into every statement (the default is QRTZ_), so a schema-qualified prefix such as QUARTZ.QRTZ_ is the mechanism; that variant was not run here. And because Oracle treats an empty string as NULL, a job described with "" reads back with a null description (measured above), which is harmless but shows up in comparisons.

The Flyway profile above is the shape of a production configuration; this is the same configuration with the variants spelled out, in the form that a deployment would use, with every secret and host name coming from the environment:

Source: Spring Boot: Quartz Scheduler. The keys are the ones of the Flyway profile that was run; the hikari line and the URL placeholders are the only additions, and the pool value follows the rule of Job Stores & Persistence.

A few remarks. The URL uses the service-name form @//host:port/service; the older host:port:SID form names an instance and is the wrong choice for a pluggable database (that form was not run). No driver-class-name is needed, Boot infers oracle.jdbc.OracleDriver from the URL. org.quartz.jobStore.misfireThreshold defaults to 60000 ms on a JDBC store (read from JobStoreSupport), so the line in the Flyway profile only restates the default. And when a second DataSource exists in the application, mark one @Primary (and the Quartz one @QuartzDataSource when it is not the primary); with no @Primary Boot silently falls back to the RAM store, even if one data source is @QuartzDataSource, as explained on the job stores page.

The five kinds of work of the Bookshelf scenario ran unchanged on Oracle: the cache refresh (a SimpleTrigger), the nightly overdue-loan reminders, the weekly statistics and the monthly retention cleanup (three CronTrigger`s), and a per-member reminder scheduled at runtime with a serialised `JobDataMap. After a run the tables held four jobs and four triggers (three cron, one simple), the dynamic job fired with its data, and after a restart with initialize-schema=never the @PersistJobDataAfterExecution counter of the overdue job carried on (5 to 6 in the Flyway run). While the scheduler is running these queries show what Quartz keeps; the epoch-millisecond columns are converted with Oracle’s NUMTODSINTERVAL:

Source: Quartz Tutorial: Lesson 9, JobStores. The statements were run against the schema of a running clustered scheduler.

Source: Quartz Tutorial: Lesson 9, JobStores. The job table showed is_durable as true for all four jobs, is_nonconcurrent as true only for overdueLoanReminders, a JOB_DATA of 273 bytes (357 for the job with a counter), one QRTZ_SCHEDULER_STATE row with checkin_interval 2000 and 532 ms since its last check-in, and the two lock rows. The cron triggers in this run used the default time zone of the JVM, Europe/Madrid, which is why the UTC fire times are offset from the cron fields; a production trigger should carry an explicit TimeZone.

Under Spring Boot the job store is LocalDataSourceJobStore, a JobStoreCMT subclass, and JobStoreCMT.initialize forces database locks on unless the data source is HSQL (the Spring class switches to an in-memory semaphore only for HSQL). So on Oracle every Quartz scheduler uses StdRowLockSemaphore, even a single non-clustered node, and a clustered one needs it by definition. The semaphore takes a row lock with exactly this statement (the constant SELECT_FOR_LOCK, with the table prefix and scheduler name filled in):

Source: StdRowLockSemaphore.java (v2.5.2) and Oracle Database SQL Language Reference: SELECT, the FOR UPDATE clause. The statement text and its execution count (9 in an eight second run with a two second check-in) come from V$SQL; the same view also showed the INSERT INTO QRTZ_LOCKS(SCHED_NAME, LOCK_NAME) VALUES (…​) that creates a lock row on first use (twice, once for STATE_ACCESS and once for TRIGGER_ACCESS; the script inserts none).

That is plain Oracle SQL, so no selectWithLockSQL override is needed (unlike SQL Server, where Quartz substitutes lock hints). The two lock names matter: TRIGGER_ACCESS guards every change of trigger state that must not interleave between nodes, and STATE_ACCESS guards the cluster check-in and the recovery of a failed node. By default acquireNextTriggers does not take TRIGGER_ACCESS at all (acquireTriggersWithinLock is false and the batch size is 1): it moves a trigger from WAITING to ACQUIRED with a conditional UPDATE and checks that one row changed, and only the following triggersFired call, which turns the acquired trigger into a fired one, runs inside the lock.

Source: JobStoreSupport.java (v2.5.2), methods acquireNextTriggers and triggersFired. The two statements are drawn from the source; the lock behaviour of the row itself is the measurement below.

Oracle’s behaviour on that row was measured with two JDBC sessions on a schema with a clustered scheduler stopped in standby: the first session ran the statement above and kept its transaction open, and the second session tried to lock the same row:

Source: Oracle Database Concepts: Data Concurrency and Consistency. The measurements are from a test run with two connections to QRTZ_LOCKS.

Two practical consequences. A session that runs SELECT …​ FOR UPDATE on QRTZ_LOCKS for inspection and is left open in a SQL client freezes the firing of every node until it ends: use plain SELECT statements (as in the queries above, which Oracle answers without locking, because readers do not block writers). And Oracle’s undo-based concurrency means the tables do not need a VACUUM-style routine for the constant insert and delete churn of QRTZ_FIRED_TRIGGERS (the PostgreSQL page has to deal with bloat there). That second point is Oracle’s documented behaviour and was not measured.

The isolation level is the Oracle default. A connection from the Hikari pool reported Connection.TRANSACTION_READ_COMMITTED (2) with auto-commit on; LocalDataSourceJobStore sets dontSetAutoCommitFalse because Spring manages the transaction around the Quartz calls (see Transaction Isolation & Locking for the Spring side). Quartz has an opt-in org.quartz.jobStore.txIsolationLevelSerializable (default false in JobStoreSupport); the Oracle documentation says that a serializable transaction raises an error instead of waiting when it meets a concurrent change, so leave it off on Oracle. That behaviour is documented, not measured here. According to the Oracle documentation the database offers only READ COMMITTED, SERIALIZABLE and read-only transactions, which is why the Quartz option offers no other level. If a lock attempt fails, StdRowLockSemaphore retries up to 3 times one second apart before it throws (maxRetry and retryPeriod, read from the source).

Clustering uses the same tables and the same row locks; the setup is the Boot properties of Clustering plus the Oracle ones of this page. It was run with two schedulers in one JVM (same scheduler name, same schema, instanceId AUTO, isClustered=true, clusterCheckinInterval=2000, three worker threads each), on tables_oracle23.sql, the default delegate and ojdbc11 23.26.3.0.0, with the first node creating the schema (always, continue-on-error at its default) and the second using never:

Source: Quartz documentation: Configure Clustering with JDBC-JobStore. The numbers are from one run of a two-node test; Quartz does not balance load, a free node takes whatever it acquires first, so the split between nodes varies from run to run.

No scheduled time ran twice and both nodes reported clustered=true. The failure-detection rules (a node is declared dead after its last check-in plus the larger of the check-in interval and its own check-in delay, plus 7500 ms), the kill-and-recover behaviour of requestsRecovery and the three-node case are measured on PostgreSQL and described on Clustering; they are engine-independent and were not repeated on Oracle. Oracle RAC (several database instances) was not available and was not tested. The cluster uses the JVM clock of each node for check-ins, not the database clock, so keep the nodes' clocks synchronised.

The test below starts Oracle Database Free in a container, lets Boot create the schema from the headless tables_oracle23.sql with continue-on-error=false (so any script error fails the test; the property exists on Spring Boot 4.x only, on 3.5.16 the initializer hard-codes true), and checks that the Bookshelf triggers are rows in Oracle and that the cache refresh fired and persisted its previous fire time. The image is large (4.81 GB) but started natively on the Apple Silicon Mac that ran the tests: the container was ready in 6 to 9 seconds and the whole test class took 17 seconds.

Source: Testcontainers: Oracle Free Module and Spring Boot: Quartz Scheduler. This class was compiled and run (1 test, 0 failures) in a test run, with src/test/resources/db/quartz/tables_oracle23_nohead.sql being the output of the sed command of The leading delete and drop statements. Its BookshelfApplication and the five Bookshelf jobs are the ones of Getting Started with Spring Boot. The password is a test value that exists only inside the throw-away container.

What was run, precisely: the gvenzl/oracle-free:slim-faststart image (Oracle Database Free, banner Oracle AI Database 26ai Free Release 23.26.3.0.0), testcontainers-oracle-free 2.0.5, ojdbc11 23.26.3.0.0 for this class, and ojdbc11 21.9.0.0 and 23.7.0.25.01 for the matrix scenarios. What matters on Oracle is the user: withUsername creates a broad application user (see A schema user on a pluggable database), so a test that wants to prove that a least-privilege user is enough must create the user itself, as the tests behind this page did with CREATE USER and the two grants. The general technique is on Spring Boot Integration Tests with Testcontainers and the full Quartz testing story on Testing.