system trigger database or dll event for EXTENT ALLOCATION

Rainer Stenzel

It would be helpful to have the opportunity to get be notified on extent allocation for checking remaining space.

Additionally maybe even a function to retrieve the size of a segments "next" extent and whether this allocation would succeed (now).

top.gun

There is already a similar facility to setup thresholds and alert.

Is the following not better than an extent alert?

For each tablespace, you can set just percent-full thresholds, just free-space-remaining thresholds, or both types of thresholds simultaneously.

Monitor with DBA_OUTSTANDING_ALERTS.

-- ie threshold within the tablespace

BEGIN

DBMS_SERVER_ALERT.SET_THRESHOLD(

   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,

   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_LE,

   warning_value           => '10240',

   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_LE,

   critical_value          => '2048',

   observation_period      => 1,

   consecutive_occurrences => 1,

   instance_name           => NULL,

   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,

   object_name             => 'USERS');

DBMS_SERVER_ALERT.SET_THRESHOLD(

   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,

   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GT,

   warning_value           => '0',

   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GT,

   critical_value          => '0',

   observation_period      => 1,

   consecutive_occurrences => 1,

   instance_name           => NULL,

   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,

   object_name             => 'USERS');

END;

/

-- check threshold settings

SELECT metrics_name, warning_value, critical_value, consecutive_occurrences

   FROM DBA_THRESHOLDS

   WHERE metrics_name LIKE '%CPU Time%';

Others: DBA_ALERT_HISTORY, DBA_OUTSTANDING_ALERTS, and DBA_THRESHOLDS.

Optionally, you can register with the DBMS_AQ.REGISTER procedure to receive an asynchronous notification when an alert is enqueued to ALERT_QUE. The notification can be in the form of email, HTTP post, or PL/SQL procedure.

Rainer Stenzel

There is already a similar facility to setup thresholds and alert.

Is the following not better than an extent alert?

For each tablespace, you can set just percent-full thresholds, just free-space-remaining thresholds, or both types of thresholds simultaneously.

Monitor with DBA_OUTSTANDING_ALERTS.

-- ie threshold within the tablespace

BEGIN

DBMS_SERVER_ALERT.SET_THRESHOLD(

   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,

   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_LE,

   warning_value           => '10240',

   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_LE,

   critical_value          => '2048',

   observation_period      => 1,

   consecutive_occurrences => 1,

   instance_name           => NULL,

   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,

   object_name             => 'USERS');

DBMS_SERVER_ALERT.SET_THRESHOLD(

   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,

   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GT,

   warning_value           => '0',

   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GT,

   critical_value          => '0',

   observation_period      => 1,

   consecutive_occurrences => 1,

   instance_name           => NULL,

   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,

   object_name             => 'USERS');

END;

/

-- check threshold settings

SELECT metrics_name, warning_value, critical_value, consecutive_occurrences

   FROM DBA_THRESHOLDS

   WHERE metrics_name LIKE '%CPU Time%';

Others: DBA_ALERT_HISTORY, DBA_OUTSTANDING_ALERTS, and DBA_THRESHOLDS.

Optionally, you can register with the DBMS_AQ.REGISTER procedure to receive an asynchronous notification when an alert is enqueued to ALERT_QUE. The notification can be in the form of email, HTTP post, or PL/SQL procedure.

Thank you,

not as sophisticated as favoured (we would like to know whether a next extent allocation will fail) but O. will probably decline furthermore proposals with reference to this feature.

Will the TABLESPACE* metric be monitored event triggered or really be checked on every observation_period ?

Is there somewhere an example for the DBMS_AQ.REGISTER notification available ?

br,

rainer stenzel

top.gun

Thank you,

not as sophisticated as favoured (we would like to know whether a next extent allocation will fail) but O. will probably decline furthermore proposals with reference to this feature.

Will the TABLESPACE* metric be monitored event triggered or really be checked on every observation_period ?

Is there somewhere an example for the DBMS_AQ.REGISTER notification available ?

br,

rainer stenzel

You could get a case where a segment needs many extents quickly causing the datafile to file.

So an alert will tell you if the next extent failed, but you may get just a milli-second of notice before it fails.

Hence such an alert is unhelpful.