Forum Stats

  • 3,874,731 Users
  • 2,266,768 Discussions
  • 7,911,961 Comments

Discussions

system trigger database or dll event for EXTENT ALLOCATION

Rainer Stenzel
Rainer Stenzel Member Posts: 64 Bronze Badge
edited Apr 15, 2016 12:47PM in Database Ideas - Ideas

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).

0 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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
    Rainer Stenzel Member Posts: 64 Bronze Badge

    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
    top.gun Member Posts: 3,666 Gold Crown

    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.