Altering or Dropping an Existing Notification Directive

Notification directives can be enabled, disabled, altered, and dropped when no longer needed.

Use ALTER DIRECTIVE to change supported notification options without dropping and recreating the directive. Use DROP DIRECTIVE to remove the directive, its notification table, and its notification view.

Prerequisites

To alter a notification directive in your schema, no additional system privilege is required. To alter a notification directive in another schema, you must have the ALTER ANY NOTIFICATION DIRECTIVE system privilege.

To drop a notification directive in your schema, no additional system privilege is required. To drop a notification directive in another schema, you must have the DROP ANY NOTIFICATION DIRECTIVE system privilege.

Alter Syntax

ALTER DIRECTIVE notification_directive_name
  [ ON { INSERT | UPDATE | DELETE }... ]
  [ WITH
      { LOGGED COLUMNS ( column_name [, column_name ]... )
          [ KEY { ( column_name [, column_name ]... ) | NONE } ]
      | KEY { ( column_name [, column_name ]... ) | NONE }
      }
  ]
  [ 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 } ];

The clauses must be specified in the order shown. Most clauses cannot be repeated.

Where:

• ON changes the tracked DML operations.
• WITH changes logged columns and key columns for table notification directives.
• KEY NONE removes the stored key and its backing-table key index.
• INCLUDING OLD VALUES and EXCLUDING OLD VALUES change whether old values are logged for table notification directives.
• CONTEXT NAMESPACE changes the SYS_CONTEXT attributes logged by the directive.
• ENABLE and DISABLE start or pause change tracking.
• RETENTION changes automatic purge behavior.

The WITH columns clause applies only to table notification directives. When you alter the logged columns, columns removed from the logged column list are dropped from the notification table, and newly logged columns are added to the notification table.

You cannot alter the USING clause. To change the notification table, notification view, or notification table tablespace, drop the directive and create a new directive.

Disabling a notification directive pauses change tracking. It does not drop or truncate the notification table.

Drop Syntax

DROP DIRECTIVE notification_directive_name;

Dropping a notification directive drops its notification table and notification view.

Examples

Consider this table notification directive:

CREATE DIRECTIVE scott.emp_ndrtv FOR scott.emp
  NOTIFY ON INSERT UPDATE DELETE
  USING NOTIFICATION TABLE emp_ntable
    TABLESPACE tbsp1
    NOTIFICATION VIEW emp_nview
  WITH LOGGED COLUMNS (empno, ename, sal)
    KEY (empno)
  INCLUDING OLD VALUES
  CONTEXT NAMESPACE userenv
    ATTRIBUTES current_user(10)
  ENABLE
  RETENTION 1 DAYS;

• Alter the DML operations:

ALTER DIRECTIVE scott.emp_ndrtv
  ON INSERT UPDATE;

• Alter the logged columns:

ALTER DIRECTIVE scott.emp_ndrtv
  WITH LOGGED COLUMNS (empno, ename)
    KEY (empno);

• Stop logging old values:

ALTER DIRECTIVE scott.emp_ndrtv
  EXCLUDING OLD VALUES;

• Start logging old values:

ALTER DIRECTIVE scott.emp_ndrtv
  INCLUDING OLD VALUES;

• Change the retention period:

ALTER DIRECTIVE scott.emp_ndrtv
  RETENTION 5 DAYS;

• Disable and then enable the directive:

ALTER DIRECTIVE scott.emp_ndrtv DISABLE;
ALTER DIRECTIVE scott.emp_ndrtv ENABLE;

• Alter context attributes:

ALTER DIRECTIVE scott.emp_ndrtv
  CONTEXT NAMESPACE userenv ATTRIBUTES current_user(10)
  CONTEXT NAMESPACE userenv ATTRIBUTES host(10);

Consider this JSON-relational duality view notification directive:

CREATE DIRECTIVE race_drtv FOR race_dv
  NOTIFY ON INSERT UPDATE DELETE
  USING NOTIFICATION TABLE race_ntable
    TABLESPACE tbsp1
    NOTIFICATION VIEW race_nview
  ENABLE
  RETENTION 1 DAYS;

• Alter the DML operations:

ALTER DIRECTIVE race_drtv
  ON INSERT UPDATE;

• Change the retention period:

ALTER DIRECTIVE race_drtv
  RETENTION 5 DAYS;

• Disable and then enable the directive:

ALTER DIRECTIVE race_drtv DISABLE;
ALTER DIRECTIVE race_drtv ENABLE;

• Drop a table notification directive:

DROP DIRECTIVE scott.emp_ndrtv;

• Drop a JSON-relational duality view notification directive:

DROP DIRECTIVE race_drtv;