Oracle DBMS_SPACE_ALERT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Alerting for Big SQL - Local Temp
AUTHID CURRENT_USER
Dependencies
WRI$_ALERT_HISTORY WRI$_ALERT_OUTSTANDING  
Documented No
First Available 12.2.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsspu.sql
Subprograms
 
DELETE_FROM_ALERT_HISTORY (new 12.2)
Delete alert history rows prior to the timestamp parameter provided dbms_space_alert.delete_from_alert_history(i_time_suggested TIMESTAMP);
exec dbms_space_alert.delete_from_alert_history(SYSTIMESTAMP-30);
 
DELETE_FROM_ALERT_OUTSTANDING (new 12.2)
  dbms_space_alert.delete_from_alert_outstanding(
i_reason_id                IN  NUMBER,
i_object_id                IN  NUMBER,
i_subobject_id             IN  NUMBER,
i_internal_instance_number IN  NUMBER,
i_con_id                   IN  NUMBER,
o_owner                    OUT VARCHAR2,
o_object_name              OUT VARCHAR2,
o_subobject_name           OUT VARCHAR2,
o_sequence_id              OUT NUMBER,
o_error_instance_id        OUT VARCHAR2,
o_state_transition_number  OUT NUMBER,
o_creation_time            OUT TIMESTAMP,
o_pdb_name                 OUT VARCHAR2);
DECLARE
 ownr    VARCHAR2(30);
 oname   VARCHAR2(30);
 soname  VARCHAR2(30);
 seqid   NUMBER;
 eiid    VARCHAR2(30);
 stnum   NUMBER;
 ctime   TIMESTAMP;
 pdbname VARCHAR2(30);
BEGIN
  dbms_space_alert.delete_from_alert_outstanding(4, 4, NULL, 1, 3, o_owner=>ownr, o_object_name=>oname, o_subobject_name=>soname, o_sequence_id=>seqid, o_error_instance_id=>eiid, o_state_transition_number=>stnum, o_creation_time=>ctime, o_pdb_name=>pdbname);
  dbms_output.put_line(ownr);
END;
/
 
INSERT_INTO_ALERT_HISTORY (new 12.2)
  dbms_space_alert.insert_into_alert_history(
p_sequence_id             IN NUMBER,
p_owner                   IN VARCHAR2,
p_object_name             IN VARCHAR2,
p_subobject_name          IN VARCHAR2,
p_reason_argument_1       IN VARCHAR2,
p_reason_argument_2       IN VARCHAR2,
p_reason_argument_3       IN VARCHAR2,
p_reason_argument_4       IN VARCHAR2,
p_reason_argument_5       IN VARCHAR2,
p_time_suggested          IN TIMESTAMP,
p_creation_time           IN TIMESTAMP,
p_action_argument_1       IN VARCHAR2,
p_action_argument_2       IN VARCHAR2,
p_action_argument_3       IN VARCHAR2,
p_action_argument_4       IN VARCHAR2,
p_action_argument_5       IN VARCHAR2,
p_message_level           IN NUMBER,
p_hosting_client_id       IN VARCHAR2,
p_process_id              IN VARCHAR2,
p_host_id                 IN VARCHAR2,
p_host_nw_addr            IN VARCHAR2,
p_instance_name           IN VARCHAR2,
p_instance_number         IN NUMBER,
p_user_id                 IN VARCHAR2,
p_execution_context_id    IN VARCHAR2,
p_error_instance_id       IN VARCHAR2,
p_resolution              IN NUMBER,
p_metric_value            IN NUMBER,
p_state_transition_number IN NUMBER,
p_reason_id               IN NUMBER,
p_pdb_name                IN VARCHAR2,
p_con_id                  IN NUMBER);
exec dbms_space_alert.insert_into_alert_history(1, USER, 'TAB$', NULL, 'ReasonArg1', 'ReasonArg2', 'ReasonArg3', 'ReasonArg4', 'ReasonArg5', SYSTIMESTAMP, SYSTIMESTAMP,'ActionArg1', 'ActionArg2', 'ActionArg3',  'ActionArg4', 'ActionArg5', 1, 'HCID', 'PID', 'HID', '192.168.1.1', 'orcl12c', 1, 'UID', 'ECID', 'EIID', 1, 2, 3, 4, 'UWAPP', 3);

col reason_argument_1 format a20

SQL> SELECT sequence_id, reason_id, owner, object_name, reason_argument_1
  2*  FROM wri$_alert_history;

SEQUENCE_ID REASON_ID  OWNER  OBJECT_NAME  REASON_ARGUMENT_1
----------- ---------- ------ ------------ --------------------
        975        135        orcl12c      orcl12c.orcl12c.
        995        136        orcl12c      orcl12c.orcl12c.
        995        136        orcl12c      orcl12c.orcl12c.
       1035        136        orcl12c      orcl12c.orcl12c.
       1035        136        orcl12c      orcl12c.orcl12c.
          1          4 SYS    TAB$         ReasonArg1
       1015        135        orcl12c      orcl12c.orcl12c.
 
INSERT_INTO_ALERT_OUTSTANDING (new 12.2)
  dbms_space_alert.insert_into_alert_outstanding (
p_object_id                IN NUMBER,
p_subobject_id             IN NUMBER,
p_internal_instance_number IN NUMBER,
p_owner                    IN VARCHAR2,
p_object_name              IN VARCHAR2,
p_subobject_name           IN VARCHAR2,
p_sequence_id              IN NUMBER,
p_reason_argument_1        IN VARCHAR2,
p_reason_argument_2        IN VARCHAR2,
p_reason_argument_3        IN VARCHAR2,
p_reason_argument_4        IN VARCHAR2,
p_reason_argument_5        IN VARCHAR2,
p_time_suggested           IN TIMESTAMP,
p_creation_time            IN TIMESTAMP,
p_action_argument_1        IN VARCHAR2,
p_action_argument_2        IN VARCHAR2,
p_action_argument_3        IN VARCHAR2,
p_action_argument_4        IN VARCHAR2,
p_action_argument_5        IN VARCHAR2,
p_message_level            IN NUMBER,
p_hosting_client_id        IN VARCHAR2,
p_process_id               IN VARCHAR2,
p_host_id                  IN VARCHAR2,
p_host_nw_addr             IN VARCHAR2,
p_instance_name            IN VARCHAR2,
p_instance_number          IN NUMBER,
p_user_id                  IN VARCHAR2,
p_execution_context_id     IN VARCHAR2,
p_error_instance_id        IN VARCHAR2,
p_context                  IN RAW,
p_metric_value             IN NUMBER,
p_reason_id                IN NUMBER,
p_state_transition_number  IN NUMBER,
p_pdb_name                 IN VARCHAR2,
p_con_id                   IN NUMBER) ;
TBD
 
UPDATE_ALERT_OUTSTANDING (new 12.2)
  dbms_space_alert.update_alert_outstanding (
i_reason_argument_1        IN  VARCHAR2,
i_reason_argument_2        IN  VARCHAR2,
i_reason_argument_3        IN  VARCHAR2,
i_reason_argument_4        IN  VARCHAR2,
i_reason_argument_5        IN  VARCHAR2,
i_time_suggested           IN  TIMESTAMP,
i_action_argument_1        IN  VARCHAR2,
i_action_argument_2        IN  VARCHAR2,
i_action_argument_3        IN  VARCHAR2,
i_action_argument_4        IN  VARCHAR2,
i_action_argument_5        IN  VARCHAR2,
i_message_level            IN  NUMBER,
i_hosting_client_id        IN  VARCHAR2,
i_process_id               IN  VARCHAR2,
i_host_id                  IN  VARCHAR2,
i_host_nw_addr             IN  VARCHAR2,
i_instance_name            IN  VARCHAR2,
i_instance_number          IN  NUMBER,
i_user_id                  IN  VARCHAR2,
i_execution_context_id     IN  VARCHAR2,
i_context                  IN  RAW,
i_metric_value             IN  NUMBER,
i_reason_id                IN  NUMBER,
i_object_id                IN  NUMBER,
i_subobject_id             IN  NUMBER,
i_internal_instance_number IN  NUMBER,
i_con_id                   IN  NUMBER,
o_owner                    OUT VARCHAR2,
o_object_name              OUT VARCHAR2,
o_subobject_name           OUT VARCHAR2,
o_sequence_id              OUT NUMBER,
o_error_instance_id        OUT VARCHAR2,
o_state_transition_number  OUT NUMBER,
o_creation_time            OUT TIMESTAMP,
o_pdb_name                 OUT VARCHAR2) ;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_SPACE
DBMS_SPACE_ALERT
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved