13
Working With System Events

System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes. Oracle lets you combine this tracking with database event notification, which provides a simple and elegant method of delivering asynchrononous messaging to an application.

This chapter includes descriptions of the various events on which triggers can be created. It also provides the list of event attribute functions.

See Also:
To use the information in this chapter, you need to understand Chapter 12, "Using Triggers".

Event Attribute Functions

You can obtain certain event-specific attributes when a trigger is fired. These attributes can be used as standalone functions.

Usage Notes

To make these attributes available, you must first run the CATPROC.SQL script.
The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.

In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_

Table 13-1 System Defined Event Attributes

Attribute	Type	Description	Example
ora_client_ip_address	VARCHAR2	Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP	if (ora_sysevent = 'LOGON') then addr := ora_client_ip_ address; end if;
ora_database_name	VARCHAR2(50)	Database name.	DECLARE db_name VARCHAR2(50); BEGIN db_name := ora_database_name; END;
ora_des_encrypted_password	VARCHAR2	The DES encrypted password of the user being created or altered.	IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table (ora_des_encypted_password); END IF;
ora_dict_obj_name	VARCHAR(30)	Name of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('Changed object is ' \|\| ora_ dict_obj_name');
ora_dict_obj_name_list (name_list OUT ora_name_ list_t)	BINARY_INTEGER	Return the list of object names of objects being modified in the event.	if (ora_sysevent = 'ASSOCIATE STATISTICS') then number_modified := ora_ dict_obj_name_list (name_list); end if;
ora_dict_obj_owner	VARCHAR(30)	Owner of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('object owner is' \|\| ora_dict_obj_ owner');
ora_dict_obj_owner_ list(owner_list OUT ora_ name_list_t)	BINARY_INTEGER	Returns the list of object owners of objects being modified in the event.	if (ora_sysevent = 'ASSOCIATE STATISTICS') then number_of_modified_ objects := ora_dict_obj_owner_ list(owner_list); end if;
ora_dict_obj_type	VARCHAR(20)	Type of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('This object is a ' \|\| ora_dict_obj_ type);
ora_grantee( user_list OUT ora_name_list_t)	BINARY_INTEGER	Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.	if (ora_sysevent = 'GRANT') then number_of_users := ora_ grantee(user_list); end if;
ora_instance_num	NUMBER	Instance number.	IF (ora_instance_num = 1) THEN INSERT INTO event_table ('1'); END IF;
ora_is_alter_column( column_ name IN VARCHAR2)	BOOLEAN	Returns true if the specified column is altered.	if (ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE') then alter_column := ora_is_ alter_column('FOO'); end if;
ora_is_creating_nested_table	BOOLEAN	Return TRUE if the current event is creating a nested table	if (ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' and ora_is_creating_nested_table) then insert into event_tab values ('A nested table is created'); end if;
ora_is_drop_column( column_ name IN VARCHAR2)	BOOLEAN	Returns true if the specified column is dropped.	if (ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE') then drop_column := ora_is_ drop_ column('FOO'); end if;
ora_is_servererror	BOOLEAN	Returns `TRUE` if given error is on error stack, `FALSE` otherwise.	IF (ora_is_servererror(error_ number)) THEN INSERT INTO event_table ('Server error!!'); END IF;
ora_login_user	VARCHAR2(30)	Login user name.	SELECT ora_login_user FROM dual;
ora_privileges( privilege_list OUT ora_name_ list_t)	BINARY_INTEGER	Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokee in the OUT parameter; returns the number of privileges in the return value.	if (ora_sysevent = 'GRANT' or ora_sysevent = 'REVOKE') then number_of_privileges := ora_privileges(priv_list); end if;
ora_revokee ( user_list OUT ora_name_list_ t)	BINARY_INTEGER	Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in the return value..	if (ora_sysevent = 'REVOKE') then number_of_users := ora_ revokee(user_list);
ora_server_error	NUMBER	Given a position(1 for top of stack), it returns the error number at that position on error stack	INSERT INTO event_table ('top stack error ' \|\| ora_server_ error(1));
ora_sysevent	VARCHAR2(20)	System event firing the trigger: Event name is same as that in the syntax.	INSERT INTO event_table (ora_ sysevent);
ora_with_grant_option	BOOLEAN	Returns true if the privileges are granted with grant option.	if (ora_sysevent = 'GRANT' and ora_with_grant_option = TRUE) then insert into event_table ('with grant option'); end if;

List of Events

Resource Manager Events

Resource manager events are related to instance startup and shutdown.Triggers created on resource manager events must be associated with the database object.

Table 13-2 contains a list of resource manager events.

Table 13-2 Resource Manager Events

Event When Fired? Conditions Restrictions Transaction Attribute Functions

Event	When Fired?	Conditions	Restrictions	Transaction	Attribute Functions
STARTUP	When the database is opened.	None allowed	No database operations allowed Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SHUTDOWN	Just before the server starts the shutdown of an instance. This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.	None allowed	No database operations allowed Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SERVERERROR	When the error `eno` occurs. If no condition is given, then this event fires when any error occurs. Does not apply to ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 conditions, because they are not true errors or are too serious to continue processing.	`ERRNO` = `eno`	Depends on the error. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_ servererror

STARTUP

When the database is opened.

None allowed

No database operations allowed

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

SHUTDOWN

Just before the server starts the shutdown of an instance.

This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.

None allowed

No database operations allowed

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

SERVERERROR

When the error eno occurs. If no condition is given, then this event fires when any error occurs.

Does not apply to ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 conditions, because they are not true errors or are too serious to continue processing.

ERRNO = eno

Depends on the error.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_
servererror

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations. For example:

CREATE OR REPLACE TRIGGER On_Logon  
  AFTER LOGON  
  ON The_user.Schema  
BEGIN  
  Do_Something;  
END;

The LOGON and LOGOFF events allow simple conditions on UID( ) and USER( ). All other events allow simple conditions on the type and name of the object, as well as functions like UID( ) and USER( ).

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot be fired later during the same transaction

Table 13-3 contains a list of client events.

Table 13-3 Client Events

Event	When Fired?	Attribute Functions
AFTER LOGON	After a successful logon of a user.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address
BEFORE LOGOFF	At the start of a user logoff	ora_sysevent ora_login_user ora_instance_num ora_database_name
BEFORE CREATE AFTER CREATE	When a catalog object is created.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events)
BEFORE ALTER AFTER ALTER	When a catalog object is altered.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_password (for ALTER USER events) ora_is_alter_column, ora_is_ drop_column (for ALTER TABLE events)
BEFORE DROP AFTER DROP	When a catalog object is dropped.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner
BEFORE ANALYZE AFTER ANALYZE	When an analyze statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS AFTER ASSOCIATE STATISTICS	When an associate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE AUDIT AFTER AUDIT BEFORE NOAUDIT AFTER NOAUDIT	When an audit or noaudit statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name
BEFORE COMMENT AFTER COMMENT	When an object is commented	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE CREATE AFTER CREATE	When an object is created.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events)
BEFORE DDL AFTER DDL	When most SQL DDL statements are issued. Not fired for ALTER DATABASE, CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL procedure interface, such as creating an advanced queue.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE DISASSOCIATE STATISTICS AFTER DISASSOCIATE STATISTICS	When a disassociate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE GRANT AFTER GRANT	When a grant statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privileges
BEFORE RENAME AFTER RENAME	When a rename statement is issued.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type
BEFORE REVOKE AFTER REVOKE	When a revoke statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privileges
BEFORE TRUNCATE AFTER TRUNCATE	When an object is truncated	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner

13Working With System Events

Event Attribute Functions

Usage Notes

Table 13-1 System Defined Event Attributes

List of Events

Resource Manager Events

Table 13-2 Resource Manager Events

Client Events

Table 13-3 Client Events

13
Working With System Events