Oracle DBMS_SPACE_ALERT
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Alerting for Big SQL - Local Temp
AUTHID DEFINER
Dependencies
WRI$_ALERT_HISTORY WRI$_ALERT_OUTSTANDING  
Documented No
First Available 12.2
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsspu.sql
Subprograms
 
DELETE_FROM_ALERT_HISTORY
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
  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
 owner   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(owner);
  dbms_output.put_line(oname);
END;
/

PL/SQL procedure successfully completed.
 
INSERT_INTO_ALERT_HISTORY
  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);

PL/SQL procedure successfully completed.

col reason_argument_1 format a20

SELECT sequence_id, reason_id, owner, object_name, reason_argument_1
FROM wri$_alert_history;

SEQUENCE_ID  REASON_ID OWNER  OBJECT_NAME  REASON_ARGUMENT_1
----------- ---------- ------ ------------ ------------------
          1          4 SYS    TAB$         ReasonArg1
 
INSERT_INTO_ALERT_OUTSTANDING
  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
  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 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx