问题描述：创建性能监测表。从数据库中找出 num_rows<=3，或者统计信息被锁定的表；并且把这些表count(*)的历史峰值，记录下来。使用定时任务加上存储过程来实现这个方式，先筛选符合条件的信息，然后把这些信息存入性能监测表MONITORING_STATISTIC；然后对这些表中得数据进行分析。
1.创建数据存储表
create table MONITORING_STATISTIC ( id number generated by default as identity, owner VARCHAR2(128), table_name VARCHAR2(128), num_rows NUMBER, object_type VARCHAR2(12), stattype_locked VARCHAR2(5), max_counts NUMBER, --初始设置成num_rows last_changed_time TIMESTAMP(6) --设置成空 ); comment on column monitoring_statistic.stattype_locked is '锁定状态'; comment on column monitoring_statistic.max_counts is 'count*历史最大值'; comment on column monitoring_statistic.last_changed_time is '插入时间'; alter table monitoring_statistic add primary key(id);
2.初始化基表
如果要再次初始化最好truncate基表，要不然会插入重复数据
create table monitoring_statistic_XX;
truncate table monitoring_statistic;
插入初始数据
insert into monitoring_statistic(owner,table_name,num_rows,object_type,stattype_locked,max_counts)
select b.*,b.num_rows
from (
select t.owner, t.table_name,NVL(t.num_rows,0) num_rows,a.object_type,a.stattype_locked from dba_tables t,DBA_TAB_STATISTICS a
where (t.num_rows <= 3 or t.num_rows = 100) and t.OWNER not IN(select username from dba_users where ORACLE_MAINTAINED = 'Y')
and t.table_name = a.table_name and t.owner=a.owner
union
select t.owner, t.table_name,NVL(t.num_rows,0) num_rows,a.object_type,a.stattype_locked from DBA_TAB_STATISTICS a,dba_tables t
where a.stattype_locked IN ('ALL', 'DATA', 'CACHE') and a.OWNER not IN(select username from dba_users where ORACLE_MAINTAINED = 'Y')
and t.table_name = a.table_name and t.owner=a.owner) b;
commit;
3.查询基表数据
col id for 999999 col table_name for a20 col partition_name for a10 col num_rows for 999999 col object_type for a20 col stattype_locked for a15 col max_counts for 999999 col last_changed_time for a30 col owner for a30 set linesize 999 select * from monitoring_statistic;
4.创建存储过程，如果使用的其他用户，要写明用户名
CREATE OR REPLACE PROCEDURE "ZHIHENGHOU"."PROC_GATHER_STAT_COUNTS"
as
t_count number;
t_num_rows number;
t_tablename varchar2(50);
t_owner varchar2(50);
t_max_counts number; --表历史最大count值
t_sql varchar2(200); --查询到的每个表的count*值
m_count number; --monitoring_statistic表行数
t_cs int; --循环计数器
CURSOR c1 IS select owner,table_name,num_rows,max_counts from ZHIHENGHOU.monitoring_statistic; --把需要的字段筛选出来
begin
select count(*) into m_count from ZHIHENGHOU.monitoring_statistic;
t_cs := 0;
FOR x IN c1 LOOP
t_tablename := x.TABLE_NAME;
t_num_rows := x.num_rows;
t_owner := x.owner;
t_max_counts := x.max_counts;
t_sql :='select count(*) from '||t_owner||'.'||t_tablename;
Execute immediate t_sql into t_count ;
if t_count > t_max_counts then
update ZHIHENGHOU.monitoring_statistic set max_counts=t_count,last_changed_time=sysdate where table_name=t_tablename and owner=t_owner;
commit;
END IF;
t_cs := t_cs+1;
DBMS_OUTPUT.PUT_LINE('The '||t_cs||' table is being checked,There are '||m_count||' tables in total!');
END LOOP;
END;
/
5.调用存储过程
set serveroutput on call PROC_GATHER_STAT_COUNTS();
6.查询监测表，如果表的count(*) 发生变化，将会被更新，记录更新时间
7.可配合定时任务使用
8.执行过程中的报错，使用其他具有DBA用户创建存储过程，执行存储过程失败，提示权限不足
是因为存储过程中Execute immediate t_sql into t_count ; Execute immediate需要在授予相关的权限
GRANT
CREATE SESSION,
CREATE ANY TABLE,
ALTER ANY TABLE,
SELECT ANY TABLE,
INSERT ANY TABLE,
UPDATE ANY TABLE,
DELETE ANY TABLE
TO ZHIHENGHOU;
标签：count,存储,rows,monitoring,第一个,秋天,num,statistic,owner From： https://www.cnblogs.com/houzhiheng/p/16854258.html