oracle表空間增加監控

==========
建立記錄表：


create table hpe_m_ts 
(
tsname char(20),
tssize number,
tsused number,
time timestamp
)
partition by range(time)
interval (numtoyminterval(1,'month'))
(partition p0 values less than (to_date('2010-01-01','yyyy-mm-dd')));


==========
cat >>/home/oracle/snow/tscheck.sql<<EOF
insert into hpe_m_ts(tsname,tssize,tsused,time)   select * from 
(SELECT
   d.tablespace_name                                    name 
  , NVL(a.bytes, 0)/1024/1024/1024                      ts_size 
  , NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024/1024    used 
  ,c.time time
FROM 
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_data_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes) bytes
      from dba_free_space
      group by tablespace_name
    ) f
  ,(
   select  sysdate  time 
       from dual
   ) c
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (
    d.extent_management like 'LOCAL'
    AND
    d.contents like 'TEMPORARY'
  )
UNION ALL 
SELECT
  d.tablespace_name                name
  , NVL(a.bytes, 0)/1024/1024/1024 ts_size
  , NVL(t.bytes, 0)/1024/1024/1024 used
  ,c.time time
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v\$temp_extent_pool
      group by tablespace_name
    ) t
,(select  sysdate  time 
       from dual
   ) c
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
ORDER BY
  2
) ts
/
EOF


=============
放到crontab：
30 6 * * * su - oracle -c "sqlplus / as sysdba </home/oracle/snow/tscheck.sql"


=============
查詢：
select to_char(time,'yyyy-mm-dd hh24:mi:ss')  time  from hpe_m_ts;
       




set pagesize 5000
COLUMN TSSIZE     FORMAT 9,999,999,999,999  HEADING 'TS Size(G)'
COLUMN TSUSED        FORMAT 9,999,999,999,999  HEADING 'Used (G)'


select  TSNAME,TSSIZE,TSUSED,to_char(time,'yyyy-mm-dd')  
from hpe_m_ts 
WHERE   time>sysdate-7     ORDER BY  TIME;




=============
查詢前一天增長情況：
set pagesize 5000  
--select tsname,sum(TSUSED) TSUSED,count(*)   from hpe_m_ts group by tsname order by TSUSED desc;
select  
TSNAME,
TSUSED-lag(TSUSED,1,0)over(order by time) as  last_increase,TSUSED,
lag(TSUSED,1,0)over(order by time) last_day_size,
to_char(time,'yyyy-mm-dd') as day  
from hpe_m_ts 
WHERE   time>sysdate-30   and tsname='&tsname';




select sum(last_increase) from (select  
TSNAME,
TSUSED-lag(TSUSED,1,0)over(order by time) as  last_increase,TSUSED,
lag(TSUSED,1,0)over(order by time) last_day_size,
to_char(time,'yyyy-mm-dd') as day  
from hpe_m_ts 
WHERE   time>sysdate-30   and tsname='&tsname') where rownum>1;


最近30添增長情況：
select sum(last_increase)  from (select  
TSNAME,
TSUSED-lag(TSUSED,1,0)over(order by time) as  last_increase,TSUSED,
lag(TSUSED,1,0)over(order by time) last_day_size,
to_char(time,'yyyy-mm-dd') as day  
from hpe_m_ts 
WHERE   time>sysdate-30   and tsname='&tsname') where LAST_DAY_SIZE<>0;




===================


select  
TSNAME,
TSUSED-lag(TSUSED,1,0)over(order by time) as  last_increase,TSUSED,
lag(TSUSED,1,0)over(order by time) last_day_size,
to_char(time,'yyyy-mm-dd') as day  
from hpe_m_ts 
WHERE   time>sysdate-10   and tsname in ('TAB_BVS','TAB_VOM') order by day,tsname;