监控表使用情况的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的内置包已经有了类似的功能，也一定要告诉我，呵呵。