Creating Notification Directives

You can create a notification directive using the SQL CREATE DIRECTIVE statement.

A notification directive creates a notification table and notification view for a table or JSON-relational duality view. The notification view exposes committed changes, including the commit SCN. In addition to the named notification table and view, Oracle database also creates internal tables for commit SCN tracking and partition management to efficiently prune data during queries. A background process performs the partition maintenance asynchronously every few seconds.

Prerequisites

The notification table and notification view are created in the directive schema. If you create a directive in another user’s schema, you can query the notification table and notification view only if you have the required object privileges on those objects.

Syntax

For a table:

CREATE DIRECTIVE notification_directive_name
  FOR table_name
  NOTIFY [ ON { INSERT | UPDATE | DELETE }... ]
  USING NOTIFICATION TABLE notification_table_name
    [ TABLESPACE notification_table_tablespace_name ]
    NOTIFICATION VIEW notification_view_name
  [ WITH [ LOGGED COLUMNS ( column_name [, column_name ]... ) ]
    [ KEY ( column_name [, column_name ]... ) ] ]
  [ INCLUDING OLD VALUES | EXCLUDING OLD VALUES ]
  [ CONTEXT NAMESPACE namespace_name
      ATTRIBUTES attribute_name [ ( length ) ]
        [, attribute_name [ ( length ) ] ]... ]...
  [ ENABLE | DISABLE ]
  [ RETENTION { integer { MINUTES | HOURS | DAYS } | NONE } ];

For a JSON-relational duality view:

CREATE DIRECTIVE notification_directive_name
  FOR json_relational_duality_view_name
  NOTIFY [ ON { INSERT | UPDATE | DELETE }... ]
  USING NOTIFICATION TABLE notification_table_name
    [ TABLESPACE notification_table_tablespace_name ]
    NOTIFICATION VIEW notification_view_name
  [ CONTEXT NAMESPACE namespace_name
      ATTRIBUTES attribute_name [ ( length ) ]
        [, attribute_name [ ( length ) ] ]... ]...
  [ ENABLE | DISABLE ]
  [ RETENTION { integer { MINUTES | HOURS | DAYS } | NONE } ];

Where:

Notification directive clauses and options

Clause What it specifies Options Notes
ON Which DML operations are tracked INSERT, UPDATE, DELETE, or a combination of these If omitted, all three operation types are tracked.
USING Names of the notification table and notification view Required notification table and notification view names; optional TABLESPACE If no tablespace is specified, the notification table is created in the schema’s default tablespace. The notification table tablespace cannot be a temporary tablespace.
WITH The logged columns and, optionally, key columns for a table notification directive LOGGED COLUMNS, KEY This clause applies only to table notification directives. The LOGGED COLUMNS clause is optional. If you do not specify it, all columns of the table are tracked. KEY is optional; if specified, the key columns must be included in the logged columns.
INCLUDING OLD VALUES / EXCLUDING OLD VALUES Whether old values of logged columns are logged for a table notification directive INCLUDING OLD VALUES or EXCLUDING OLD VALUES If omitted, old-value columns are not added.
CONTEXT NAMESPACE SYS_CONTEXT attributes to log Namespace name, attribute names, and optional attribute lengths Attribute length defaults to 64 bytes and can range from 1 to 4000 bytes.
ENABLE / DISABLE Whether the directive should track changes ENABLE or DISABLE Default is ENABLE.
RETENTION Whether notification history is purged automatically A value in MINUTES, HOURS, or DAYS, or NONE (example: 7 DAYS) If omitted, the behavior is RETENTION NONE, and you must purge notification history manually using DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE.

Notification Behavior

Notification rows are visible only after the transaction commits. Oracle efficiently buffers the transaction changes in memory and efficiently inserts batches of changes into the notification table when the buffer fills up or when the transaction commits.

For an ordinary table notification directive, if you omit LOGGED COLUMNS, Oracle logs all eligible, non-internal-hidden columns of the base table. For a JSON collection table, the default logged columns are DATA and RESID. KEY is optional. If you specify KEY columns, they do not need to be primary-key or unique-key columns, but they must be included in the logged columns. Oracle creates a non-unique index on the KEY columns.

INCLUDING OLD VALUES adds an OLD$column_name column for each logged column. For an insert, an old-value column is null. For an update or delete, an old-value column contains the value before the operation. If EXCLUDING OLD VALUES is specified, or if the old-value clause is omitted, old-value columns are not added.

For JSON-relational duality view notification directives, do not specify the WITH columns clause. Oracle logs the columns ID and DATA of the duality view. The ID column contains the object’s _id value. DELETE does not populate DATA. DML against tables underlying a JSON-relational duality view can generate notifications. Oracle logs only the final committed object for each affected object.

For the CONTEXT NAMESPACE clause, specify a SYS_CONTEXT namespace and one or more attributes. Built-in namespaces, such as USERENV, can be used directly. For an application context, set attribute values through the mechanism defined for that context, typically a trusted package that calls DBMS_SESSION.SET_CONTEXT. Namespace and attribute names are case-insensitive and must be valid identifiers. The generated column name, CONTEXT$namespace$attribute, must fit within the maximum identifier length. For duality view notifications, because the changes are logged at commit time, the SYS_CONTEXT attribute values are read during transaction commit time and written to the notification table.

Use the notification view to read notification history:

SELECT *
  FROM notification_view_name;

Retention

Use the RETENTION clause to make notification history eligible for automatic purge. Supported units are MINUTES, HOURS, and DAYS.

The minimum retention value is 1 minute. The maximum retention value is 100 years. A value of 0 is not allowed. Mixed units, such as 1 MINUTES 30 SECONDS, are not allowed.

Specify RETENTION NONE to disable automatic retention-based purge. If you omit the RETENTION clause from CREATE DIRECTIVE, the default is RETENTION NONE. You can purge notification history manually using DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE regardless of the directive’s retention setting.

Automatic and manual purges are asynchronous. Oracle does not delete each row as soon as it becomes purge-eligible or when you run DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE. Oracle purges efficiently by dropping eligible notification table partitions.

Restrictions

The following restrictions apply to notification directives:

If the notification table tablespace fills up, DML on the tracked object raises an error.

The following bulk DDL operations are prohibited on a table if a notification directive exists on the table or on a JSON-relational duality view that uses the table:

Related Topics