Oracle数据量监控存储过程

最新推荐文章于 2024-07-24 06:35:46 发布

木及言风

最新推荐文章于 2024-07-24 06:35:46 发布

阅读量341

点赞数

文章标签： oracle 数据库 sql Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_56496488/article/details/129262719

版权

先来个日表

create table QDSJZX_SJL_NB
(
  bm   VARCHAR2(900),
  sjl  NUMBER,
  tjsj DATE default sysdate,
  id   VARCHAR2(32) default sys_guid()
);

comment on column QDSJZX_SJL_NB.bm
  is '表名';
comment on column QDSJZX_SJL_NB.sjl
  is '数据量';
comment on column QDSJZX_SJL_NB.tjsj
  is '统计时间';
comment on column QDSJZX_SJL_NB.id
  is '主键';

再用同样表结构复制一个历史表

再执行下面的存储过程，调整一下你要监控的表名，再做个定时

CREATE OR REPLACE PROCEDURE P_qdsjzx_sjl_nb AS
        v_sql varchar2(2000);
        V_DATAQUANTITY NUMBER; --现有数据(条)
begin
execute immediate 'truncate table qdsjzx_sjl_nb';--先清空日表
  --循环开始
       for cloop in (select table_name from user_tables--用户中的表和你要监控的表名做个交集
intersect
select * from (
select distinct tablename from t_statisticalquery
union
select distinct tablename from qdsjzx_qml
union
select distinct bm from qdsjzx_sjl)
 ) loop
 
  v_sql := 'select count(*) from ' ||cloop.table_name;
     --dbms_output.put_line(V_SQL);
     execute immediate v_sql  into V_DATAQUANTITY ;

insert into qdsjzx_sjl_nb(bm,sjl) values(cloop.table_name,V_DATAQUANTITY);
commit;--循环插入日表
end loop;
insert into qdsjzx_sjl_nb_ls select * from qdsjzx_sjl_nb;
commit;--插入历史表
end;