--创建序列
CREATE SEQUENCE oracle_monitor_SEQUENCE MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
--创建监控记录表
create table oracle_monitor_log(
id NUMBER(18),
event_name varchar2(60),
event_type varchar2(60),
event_date TIMESTAMP,
ip_addr varchar2(60),
op_user varchar2(60),
event_desc varchar2(128),
CONSTRAINT PK_ORALCE_MONITOR_LOG PRIMARY KEY (ID));
--创建修改用户操作的触发器
CREATE OR REPLACE TRIGGER oracle_monitor_alter_user
BEFORE ALTER
ON DATABASE
BEGIN
IF ( ora_dict_obj_type = 'USER'
--AND ora_dict_obj_name = ora_login_user
--AND ora_login_user != 'SYS'
--AND ora_login_user != 'SYSTEM'
) THEN
insert into oracle_monitor_log (id,event_name,event_type ,ip_addr ,op_user,event_date ,event_desc ) values(oracle_monitor_SEQUENCE.NEXTVAL ,ora_sysevent,ora_dict_obj_type ,sys.utl_inaddr.get_host_address,ora_login_user,sysdate,ora_dict_obj_name||' '||ora_des_encrypted_password);
END IF;
END;
/
--创建数据库启动的触发器
create or replace trigger oracle_monitor_startup
after startup on database
begin
insert into oracle_monitor_log (id,event_name,event_type ,ip_addr ,op_user,event_date ) values(oracle_monitor_SEQUENCE.NEXTVAL ,ora_sysevent,ora_dict_obj_type ,sys.utl_inaddr.get_host_address,ora_login_user,sysdate);
end;
/
--创建数据库关闭的触发器
create or replace trigger oracle_monitor_shutdown
before shutdown on database
begin
insert into oracle_monitor_log (id,event_name,event_type ,ip_addr ,op_user,event_date ) values(oracle_monitor_SEQUENCE.NEXTVAL ,ora_sysevent,ora_dict_obj_type ,sys.utl_inaddr.get_host_address,ora_login_user,sysdate);
end;
/