==========
建立记录表：
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<
(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
=============
查询：
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;