col day format a15;
col hour format a4;
col total format 999;
select
to_char(first_time,'yyyy-mm-dd') day,
to_char(first_time,'hh24') hour,
count(*) total
from
v$log_history
group by
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by
to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
asc;
Clean up and create a monitoring table, check no triggers with name TRG_%
drop table log_dml;
purge recyclebin;
CREATE TABLE log_dml (dml_time timestamp, username varchar2(32), sid number, information varchar2(64));
select count(*) from user_triggers where trigger_name like 'TRG_%';
Create triggers for every table.
DECLARE
CURSOR all_tables
IS
SELECT object_name, object_id, object_type
FROM user_objects WHERE object_type = 'TABLE'
AND object_name not like 'LOG_DML';
BEGIN
FOR rec_cur IN all_tables
LOOP
EXECUTE IMMEDIATE 'create or replace trigger trg_'
|| rec_cur.object_id
|| ' before insert or update or delete on '
|| rec_cur.object_name
|| '
declare
begin
if UPDATING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''UPDATING on '||rec_cur.object_name||''');
elsif DELETING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''DELETING on '||rec_cur.object_name||''');
elsIF INSERTING then
insert into log_dml values(sysdate, sys_context(''USERENV'',''CURRENT_SCHEMA''), sys_context(''USERENV'',''SID''), ''INSERTING on '||rec_cur.object_name||''');
end if;
end;';
END LOOP;
END;
/
To check and remove the triggers.
select count(*) from user_triggers where trigger_name like 'TRG_%';
DECLARE
CURSOR all_triggers
IS
SELECT trigger_name
FROM user_triggers WHERE trigger_name like 'TRG_%';
BEGIN
FOR rec_cur IN all_triggers
LOOP
EXECUTE IMMEDIATE 'drop trigger '|| rec_cur.trigger_name ||'';
END LOOP;
END;
/
