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

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria