ALTER DIRECTIVE (NOTIFY)

Purpose

Use ALTER DIRECTIVE to modify a notification directive. You can change the DML operations that it tracks, logged and key columns for a table directive, context attributes, whether the directive is enabled, and its retention period. You can specify more than one of these changes in a statement.

Prerequisites

The directive must be in your schema or you must have the ALTER ANY NOTIFICATION DIRECTIVE system privilege.

Syntax

alter_directive::=

Note: Specify at least one of the following: the ON clause, notification_columns_clause, sys_context_clause, ENABLE or DISABLE, or retention_clause.

Semantics

For the meanings and restrictions of these clauses, see CREATE DIRECTIVE (NOTIFY).

The ON clause changes which INSERT, UPDATE, and DELETE operations are tracked.

The WITH clause changes the logged columns, key columns, or old-value logging for a notification directive on a table. If you remove a column from the logged column list, Oracle drops it from the notification table. If you add a column to the list, Oracle adds it to the notification table. You can also specify INCLUDING OLD VALUES or EXCLUDING OLD VALUES by itself to change old-value logging.

The CONTEXT NAMESPACE clause changes the SYS_CONTEXT attributes recorded with notifications.

ENABLE starts change tracking, and DISABLE pauses it. Disabling a directive does not drop or truncate its notification table.

The RETENTION clause changes the retention period. Specify RETENTION NONE to stop automatic purging and use manual purging.

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

Examples

The following statement creates a notification directive on the scott.emp table:

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 ON Clause

The following statement changes the directive to track only inserts and updates:

ALTER DIRECTIVE scott.emp_ndrtv ON INSERT UPDATE;

Alter the Logged Columns

The following statement logs only the empno and ename columns:

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

Exclude Old Values

The following statement stops tracking old values:

ALTER DIRECTIVE scott.emp_ndrtv EXCLUDING OLD VALUES;

Change the Retention Period

The following statement makes notification entries eligible for automatic purge after five days:

ALTER DIRECTIVE scott.emp_ndrtv RETENTION 5 DAYS;

Disable the Directive

The following statement disables change tracking. Specify ENABLE to resume tracking:

ALTER DIRECTIVE scott.emp_ndrtv DISABLE;

Alter Context Attributes

The following statement tracks additional SYS_CONTEXT information:

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