ORACLE 8I SYSTEM EVENT TRIGGER (ORACLE 8.1.6)

출처 :  https://kr.forums.oracle.com/forums/thread.jspa?threadID=464026

Explanation
======================

Oracle 8i에서는 LOGON 이나 SHUTDOWN 과 같은 시스템 상의 변화를 추적할 수 있는
system event를 제공한다. 다음은 system event 발생 시 가용한 function 목록과
활용 예제이다. (Oracle 8.1.6 기준 자료 입니다.)

1. System Defined Event Attributes

다음은 시스템에서 제공하는 function ( attribute )들의 목록이다.
아래에 나열된 function은 데이터베이스 생성시 CATPROC.SQL이 실행되면서 생성이 되며,
SYS 소유이나 public synonym 형태로 제공되어 모든 사용자가 이용할 수 있다.

ora_client_ip_address,
ora_database_name,
ora_des_encrypted_password,
ora_dict_obj_name,
ora_dict_obj_name_list,
ora_dict_obj_owner,
ora_dict_obj_owner_list,
ora_dict_obj_type,
ora_grantee,
ora_instance_num,
ora_is_alter_column,
ora_is_creating_nested_table,
ora_is_drop_column,
ora_is_servererror,
ora_login_user,
ora_privileges,
ora_revokee,
ora_server_error,
ora_sysevent,
ora_with_grant_option



2. Event의 종류

1) Resource Manager Event

STARTUP, SHUTDOWN, SERVERERROR


2) Client Event

AFTER LOGON, BEFORE LOGOFF, BEFORE CREATE, AFTER CREATE,
BEFORE ALTER, AFTER ALTER, BEFORE DROP, AFTER DROP,
BEFORE ANALYZE, AFTER ANALYZE, BEFORE ASSOCIATE STATISTICS,
AFTER ASSOCIATE STATISTICS, BEFORE AUDIT, AFTER AUDIT,
BEFORE NOAUDIT, AFTER NOAUDIT, BEFORE COMMENT, AFTER COMMENT,
BEFORE CREATE, AFTER CREATE, BEFORE DDL, AFTER DDL,
BEFORE DISASSOCIATE STATISTICS, AFTER DISASSOCIATE STATISTICS,
BEFORE GRANT, AFTER GRANT, BEFORE RENAME, AFTER RENAME,
BEFORE REVOKE, AFTER REVOKE, BEFORE TRUNCATE, AFTER TRUNCATE




3. 예제 : Client의 IP address logging

LOGON system event를 사용하여 SCOTT 유저에 logon/logoff 하는 클라이언트의
IP address를 특정 table에 logging 하는 예이다. ( Oracle 8.1.5 에서는
ora_client_ip_address function이 가용하지 않으며 Oracle 8.1.6 부터 가용 )

drop table login_statistics
/

create table login_statistics (
ip_address varchar2(15),
when date,
what varchar2(12)
)
/

create or replace trigger on_lonon
after logon
on scott.schema
declare
v_ip_address varchar2(16);
begin
v_ip_address := ora_client_ip_address;
insert into login_statistics values
( v_ip_address, sysdate, 'LOGIN' ) ;
end;
/

create or replace trigger before_logoff
before logoff
on scott.schema
declare
v_ip_address varchar2(16);
begin
v_ip_address := ora_client_ip_address;
insert into login_statistics values
( v_ip_address, sysdate, 'LOGOFF' ) ;
end;
/


위의 trigger생성결과 client의 logon/logoff 결과를 다음과 같이 볼 수 있다.

SQL> alter session set nls_date_format ='yyyy/mm/dd:hh24:mi:ss';
SQL> select * from login_statistics;

IP_ADDRESS WHEN WHAT
--------------------------------
2000/05/10:11:03:31 LOGOFF
2000/05/10:11:04:00 LOGIN
2000/05/10:11:04:45 LOGOFF
2000/05/10:11:05:01 LOGIN
2000/05/10:11:06:26 LOGOFF
152.69.41.120 2000/05/10:11:10:01 LOGIN
2000/05/10:11:10:15 LOGIN
152.69.41.120 2000/05/10:11:11:40 LOGOFF


IP_ADDRESS 부분에 정보가 없는 경우는 server side의 sqlplus등으로 접속한 경우이다.