秋天的第一个存储过程

问题描述：创建性能监测表。从数据库中找出 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;