收集每日物件資料量情況

luckyfriends發表於2014-01-24
物件


文章版權所有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

>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1074829/，如需轉載，請註明出處，否則將追究法律責任。

相關文章