Oracle System Events and Client Events

Oracle Events are the events that are executed in oracle instance or user schema (DDL ,DCL Operations)

There are two types of events

A- Oracle System Events

   STARTUP, SHUTDOWN and SERVERERROR

B- Client Events

    LOGON, LOGOFF, DB_ROLE_CHANGE, SUSPEND, ALTER, DROP, CREATE, ANALYZE, AUDIT, NONAUDIT, DDL, GRANT, REVOKE and TRUNCATE.

In any of previous events I can develop database triggers on database or schema  for tracking, monitoring, logging or do any action.

For example( Trigger after startup database)

CREATE OR REPLACE TRIGGER XXX_TRG
   AFTER STARTUP
   ON DATABASE
BEGIN
   NULL;
--Write your code here
END; 

Oracle Events Attributes
Oracle provide system event attribute that are available in any trigger of previous types.

I will List then and give short description for everyone

 

1-  ora_client_ip_address

     Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP

2- ora_database_name

      Return database name

3- ora_des_encrypted_password

     Retutn DES encrypted password of the user being created or altered.

4- ora_dict_obj_name

    Name of the dictionary object on which the DDL operation occurred

5- ora_dict_obj_owner

    Owner of the dictionary object on which the DDL operation occurred.

6- ora_dict_obj_type

     Type of the dictionary object on which the DDL operation occurred

7- ora_login_user

     Login user name

8- ora_sysevent

     System event firing the trigger( Create, Drop, .....)

There are a lot of attributes other than listed previous, you can get it from Oracle Documentation.
But I guess these previous attributes are the most used .

I plan develop logging DDL Operations in database, So in the near future I will post new article that will use ideas listed in this article.

Mahmoud Ahmed El-Sayed