Oracle | DBMS_SPACE

Oracle DBMS_SPACE_ALERT Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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