Purging Notification History

Notification history can be purged automatically by retention or manually with DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE.

Automatic Purge

Use the RETENTION clause on CREATE DIRECTIVE or ALTER DIRECTIVE to make notification table rows eligible for automatic purge.

ALTER DIRECTIVE scott.emp_ndrtv
  RETENTION 5 DAYS;

Supported retention units are MINUTES, HOURS, and DAYS. The minimum retention value is 1 minute. The maximum retention value is 100 years.

Specify RETENTION NONE to disable automatic retention-based purge. If RETENTION is omitted from CREATE DIRECTIVE, the default is RETENTION NONE. If it is omitted from ALTER DIRECTIVE, the directive’s existing retention setting remains unchanged.

Notification history can be purged manually regardless of the directive’s retention setting.

ALTER DIRECTIVE scott.emp_ndrtv
  RETENTION NONE;

Automatic purge is asynchronous and is performed by a database background process. Oracle purges by dropping eligible notification table partitions rather than deleting individual rows, and can defer the physical purge.

Manual Purge

Use DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE to manually purge notification table history before a specified SCN.

PROCEDURE purge_notification_table(
  notification_directive_name IN VARCHAR2,
  purge_scn                   IN NUMBER);

notification_directive_name is the notification directive name. purge_scn is the SCN before which notification table data can be purged.

You can call this procedure whether or not the directive has a RETENTION clause.

Manual purge is monotonic: a request advances the directive’s manual purge SCN only when the specified SCN is greater than the previously recorded value. A purge_scn less than or equal to the previous value has no effect.

If both retention and a manual purge SCN apply, Oracle uses the greater of the retention-derived SCN and the manual purge SCN.

Purging is asynchronous and partition-granular. Oracle drops eligible notification table partitions and can defer the physical purge, rather than deleting individual rows immediately.

For example:

BEGIN
  DBMS_DIRECTIVES.PURGE_NOTIFICATION_TABLE(
    notification_directive_name => 'SCOTT.EMP_NDRTV',
    purge_scn                   => 123456789);
END;
/

Prerequisites