监控表使用情况的Package

 

今天编写了一个Oracle的Package，分享给大家。

背景是这样的：现有的系统是从其他公司的系统移植过来的，因此有很多表都是对原来的那个公司定制的，而在移植过来之后，因为不适合业务的需求，所以就没有使用，而长期以来也没有人对其加以整理，因此造成系统中有很多冗余的表，这对于系统的维护造成了很多不便，所以想要看看系统中到底哪些表是根本没有使用的，对于这些表检查出来之后，要做删除。（当前系统中有2400多个表啊，初步估计其中大约有一半以上都是出于不使用的状态）

Package里面的内容比较简单，就是先建立了一个表，用来存储所有表以及是否使用的状态。初始的时候，会把系统中所有表抓过来，然后把使用状态都设置为N，也就是没有被使用。

 

代码

-- 取得所有的表，并将其放到监控的表中
procedure get_all_tables(v_owner in varchar2 ) is
begin
insert into table_usage
(
owner
,table_name
,is_using
,modify_date
)
select A.OWNER
,A.TABLE_NAME
, ' N ' -- 初始的时候默认为N
,SYSDATE
from all_all_tables a
where a.owner = v_owner
and a.status = ' VALID ' ;

commit ;
end get_all_tables;

 

 

 

然后会在每个表上建立一个触发器，当对于指定的表，有插入操作的时候，就会修改表table_usage，将使用标志设置为Y，也就是正在使用的状态。

 

代码

-- 为所有表创建触发器，以进行监控
-- 根据table_usage表中的情况来创建
procedure create_all_triggers is
cursor cur_tables is
select owner
,table_name
from table_usage
where is_using = ' N ' ;
strCreateSql varchar2 ( 32767 );
begin
-- 遍历所有表，为其创建系统触发器，从而监控使用的情况
for rec_tables in cur_tables loop
strCreateSql : = '' ;
strCreateSql : = strCreateSql || ' create or replace trigger tri_MU_ ' || rec_tables.owner || ' _ ' || rec_tables.table_name || ' ' ;
strCreateSql : = strCreateSql || ' after insert on ' || rec_tables.owner || ' . ' || rec_tables.table_name || ' ' ;
strCreateSql : = strCreateSql || ' for each row ' ;
strCreateSql : = strCreateSql || ' ' ;
strCreateSql : = strCreateSql || ' begin ' ;
strCreateSql : = strCreateSql || ' ' ;
strCreateSql : = strCreateSql || ' update table_usage ' ;
strCreateSql : = strCreateSql || ' set is_using = '' Y '' ' ;
strCreateSql : = strCreateSql || ' ,modify_date = sysdate ' ;
strCreateSql : = strCreateSql || ' where owner = ''' || rec_tables.owner || ''' ' ;
strCreateSql : = strCreateSql || ' and table_name = ''' || rec_tables.table_name || ''' ; ' ;
strCreateSql : = strCreateSql || ' ' ;
strCreateSql : = strCreateSql || ' end tri_MU_ ' || rec_tables.owner || ' _ ' || rec_tables.table_name || ' ; ' ;
execute immediate strCreateSql;
end loop;
end create_all_triggers;

 

 

 

最后需要定期清理这些触发器，尽管不会浪费太多的性能，但毕竟也会浪费，所以就有一个清理触发器的过程：

 

清理触发器

-- 为确定正在使用的表清理触发器
procedure clear_triggers is
cursor cur_used_tables is
select owner
,table_name
from table_usage A
where A.is_using = ' Y ' ;
strClearTriggerSql varchar2 ( 32767 );
begin
for rec_used_table in cur_used_tables loop
strClearTriggerSql : = ' drop trigger tri_MU_ ' || rec_used_table.owner || ' _ ' || rec_used_table.table_name || ' ; ' ;
execute immediate strClearTriggerSql;
end loop;
exception
when others then
dbms_output.put_line( ' there is an exception -- trigger not exists-- ' );
end clear_triggers;

 

 

 

需要说明的有两点：

1、因为需要在Package内执行创建和删除触发器的操作，所以需要确保有执行该项操作的权限。

2、本来想要试着在table_usage表上增加一个触发器，一旦使用标志由N变为Y，那么就自动删除相应的表上的触发器，但是没能实现，有时间继续试验。

欢迎大家多提意见，或者说Oracle的内置包已经有了类似的功能，也一定要告诉我，呵呵。

 

转载于:https://www.cnblogs.com/houbowei/archive/2009/12/02/1615664.html