oracle取平均每日交易量,收集每日对象数据量情况

文章版权所有Jusin Hao(luckyfriends)，支持原创，转载请注明。

--创建记录数据量的表

create table cux_datasize

(tb_lb_size number,idx_size number,lbidx_size number,allseg_size number,query_date DATE)

tablespace PSDEFAULT;

---创建收集的存储过程

CREATE OR REPLACE PROCEDURE p_cux_datasize AS

v_tb_lb_size   number := 0;

v_idx_size number := 0;

v_lbidx_size   number := 0;

v_allseg_size number :=0;

v_query_date date :=null;

BEGIN

Select SUM(a.Bytes) / 1024 / 1024 tb_lb_size into v_tb_lb_size

From User_Segments a, User_Lobs b

Where a.Segment_Name = b.segment_name(+)

And a.segment_type <> 'INDEX'

And a.segment_type <> 'LOBINDEX' ;

Select SUM(a.Bytes) / 1024 / 1024 idx_size into v_idx_size

From User_Segments a, User_Lobs b

Where a.Segment_Name = b.segment_name(+)

And a.segment_type = 'INDEX';

Select SUM(a.Bytes) / 1024 / 1024 lbidx_size into v_lbidx_size

From User_Segments a, User_Lobs b

Where a.Segment_Name = b.segment_name(+)

And a.segment_type = 'LOBINDEX';

Select SUM(a.Bytes) / 1024 / 1024 allseg_size into v_allseg_size

From User_Segments a, User_Lobs b

Where a.Segment_Name = b.segment_name(+);

select sysdate into v_query_date from dual;

insert into cux_datasize values(v_tb_lb_size,v_idx_size,v_lbidx_size,v_allseg_size,v_query_date);

commit;

end;

/

----授予SYSADM执行DBMS_JOB权限

[oracle@hrapp2 ~]$ sqlplus / as sysdba

SQL> grant execute on DBMS_JOB to SYSADM;

Grant succeeded.

----创建定时job每日收集数据量(第一次当天2点执行，每天2点执行一次)

conn SYSADM/*******

VARIABLE JOBNO NUMBER;

--VARIABLE INSTNO NUMBER;

BEGIN

--SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE; (因为sysadm没有查询改视图的权限)

DBMS_JOB.SUBMIT(:JOBNO,'SYSADM.P_CUX_DATASIZE;',TRUNC(SYSDATE)+1+2/24,'TRUNC(SYSDATE)+1+2/24',TRUE,’1’);

COMMIT;

END;

/

----修改job

begin

sys.dbms_job.change(job => 144,

what => 'SYSADM.P_CUX_DATASIZE;',

next_date => to_date('10-12-2013 02:00:00', 'dd-mm-yyyy hh24:mi:ss'),

interval => 'TRUNC(SYSDATE)+7+2/24');

commit;

end;

/

注意：

之所以赋予sysadm执行DBMS_JOB的权限，因为如果用system创建job，而存储过程是sysadm(包括其中访问的对象)创建，那么执行job会报错：

ORA-12011:无法执行1作业

ORA-06512:在"SYS.DBMS_IJOB",line406

ORA-06512:在"SYS.DBMS_JOB",line272

ORA-06512:在line1

要么就得改写存储过程。 一个用户job无法调用另一个用户的存储过程。

SQL> select * from cux_datasize where query_date >to_date('2014-01-17','YYYY-MM-DD') order by query_date;

TB_LB_SIZE   IDX_SIZE LBIDX_SIZEALLSEG_SIZE QUERY_DATE

---------- ---------- ---------- ----------- -----------

57316.5  16798.625     35.25   74150.375 2014-01-17

57327.5625  16800.625      35.25 74163.4375 2014-01-18

57391.625 16803.6875      35.25 74230.5625 2014-01-19

57399.625 16806.6875      35.25 74241.5625 2014-01-20

57412.625 16812.6875      35.25 74260.5625 2014-01-21

57413.875   16814.75     35.25   74263.875 2014-01-22

57414.9375 16816.8125      35.25       74267 2014-01-23

57428.9375  16821.375      35.25 74285.5625 2014-01-24

单位为M,一周增长数据：

select (select a.allseg_size

from cux_datasize a

where TRUNC(a.query_date) = TRUNC(SYSDATE)) -

(select a.allseg_size

from cux_datasize a

where TRUNC(a.query_date) = TRUNC(SYSDATE) - 7) || 'M' as Growing_datasize_week

from dual;

GROWING_DATASIZE_WEEK

-----------------------------------------

135.1875M

单位为M，一个月增长数据(上月6日到这月10日)：

select (select a.allseg_size

from cux_datasize a

where TRUNC(a.query_date) = TRUNC(SYSDATE)) -

(select a.allseg_size

from cux_datasize a

where TRUNC(a.query_date) = TRUNC(SYSDATE) - 30) || 'M' asGrowing_datasize_week

from dual;

GROWING_DATASIZE_WEEK

-----------------------------------------

520.375M

>