Snapshot Mode

To avoid delayed execution in such a scenario, you can run your queries to the system tables in snapshot mode. Snapshot mode ignores existing transaction locks on system tables and does not create new ones. All the queries to the system tables will run in read-only mode and show you the latest object version (latest committed version).

Using the Snapshot Mode

The snapshot mode is enabled for the system tables by default by parameter SNAPSHOT_MODE, therefore, all your queries to the system tables will run in snapshot mode. The parameter determines how all queries within the session are executed. You can change the behavior either by using ALTER SYSTEM or ALTER SESSION.

You can enable or disable the snapshot mode for the current session using ALTER SESSION. If the snapshot mode is enabled, all queries to the system table run in snapshot mode during the current session.

Examples

--enable snapshot mode for system tables in a session
ALTER SESSION SET SNAPSHOT_MODE = 'SYSTEM TABLES';

-- disable snapshot mode for system tables in a session
ALTER SESSION SET SNAPSHOT_MODE = 'OFF';

You can enable or disable the snapshot mode for all new sessions using ALTER SYSTEM. If the snapshot mode is enabled, all queries to the system table run in snapshot mode.

Examples

--disable snapshot mode for system tables
ALTER SYSTEM SET SNAPSHOT_MODE = 'OFF';

--enable snapshot mode for system tables
ALTER SYSTEM SET SNAPSHOT_MODE = 'SYSTEM TABLES';

Using Special Parameter in Driver Connection String

You can specify a parameter in your driver connection string to enable the snapshot mode for your connection. You can then run all queries to the system tables in snapshot mode. The driver parameters are an option to initialize the session parameter to a different value than what's already specified by ALTER SYSTEM.

For more details on the parameter, see Supported driver properties (JDBC), Connection Strings (ODBC), or Use the Data Provider (ADO.NET).

Using /*snapshot execution*/ Prefix

You can also run any SQL statement in snapshot mode using /*snapshot execution*/ prefix with an SQL statement. The prefix overrules the value set for the session parameter. When you run a statement with prefix /*snapshot execution*/, only that statement runs in snapshot mode. The behavior of this method is different from all the above methods because with this prefix, the whole query runs in snapshot mode and it's not limited to system tables anymore.

Using prefix is not supported for EXECUTE SCRIPT statement.

The prefix is case-sensitive and must always be at the beginning of a statement and not be preceded by any other comment or white space. The first 22 characters of the statement must be /*snapshot execution*/. If the SQL preprocessor is activated, then only the statement text after the preprocessing is checked.

Example

/*snapshot execution*/ 
SELECT * FROM customers;
-- the prefix before the statement allows you to run the command in snapshot mode

Behavior Differences

The following table describes the behavior differences when snapshot is on, off, or run with prefix.