Oracle | DBMS_SPACE

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	INSERT_INTO_ALERT_HISTORY	UPDATE_ALERT_OUTSTANDING
DELETE_FROM_ALERT_OUTSTANDING	INSERT_INTO_ALERT_OUTSTANDING

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