Monitoring on DROP USER

conn / as sysdba

create user abc identified by abc;

CREATE TABLE ddl_log (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);

CREATE OR REPLACE TRIGGER bds_trigger BEFORE DROP ON DATABASE
DECLARE 
oper system.ddl_log.operation%TYPE;
BEGIN 
INSERT INTO system.ddl_log 
SELECT ora_sysevent, 
ora_dict_obj_owner, 
ora_dict_obj_name, 
NULL, 
USER, 
SYSDATE
FROM dual;
END bds_trigger;

/

-- now let's test the logging mechanism 
select * from system.ddl_log;

drop user abc;

select * from system.ddl_log;

select * from DBA_USERS where lower(username) like lower('te%');
Advertisements
Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: