Oracle查看表空间的每日增长量

查看表空间占用情况：

set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;


历史数据库增量，适用各版本oracle

SELECT day,total_GB,used_GB,total_GB-used_GB free_GB,round(100*used_GB/total_GB,2) used_percent,
case when(used_GB=used_GB-LAG(used_GB,1,0)OVER(ORDER BY day)) then null else used_GB-LAG(used_GB,1,0)OVER(ORDER BY day) end incr_GB
from
(select to_char(snap.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff') day,
round(sum(tsu.TABLESPACE_SIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) total_GB,
round(sum(tsu.TABLESPACE_USEDSIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) used_GB
from DBA_HIST_TBSPC_SPACE_USAGE tsu,
DBA_HIST_SNAPSHOT snap,
V$TABLESPACE vt,
DBA_TABLESPACES dt
where tsu.SNAP_ID = snap.SNAP_ID
and tsu.DBID = snap.DBID
and snap.instance_number = 1
and tsu.TABLESPACE_ID = vt.TS#
and vt.NAME = dt.TABLESPACE_NAME
and SUBSTR(to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),12,5)='00:00'
group by snap.END_INTERVAL_TIME
order by snap.END_INTERVAL_TIME desc) a
order by day desc;


不包含某些表空间的参考：

SELECT day,total_GB,used_GB,total_GB-used_GB free_GB,round(100*used_GB/total_GB,2) used_percent,
case when(used_GB=used_GB-LAG(used_GB,1,0)OVER(ORDER BY day)) then null else used_GB-LAG(used_GB,1,0)OVER(ORDER BY day) end incr_GB
from
(select to_char(snap.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff') day,
round(sum(tsu.TABLESPACE_SIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) total_GB,
round(sum(tsu.TABLESPACE_USEDSIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) used_GB
from DBA_HIST_TBSPC_SPACE_USAGE tsu,
DBA_HIST_SNAPSHOT snap,
V$TABLESPACE vt,
DBA_TABLESPACES dt
where tsu.SNAP_ID = snap.SNAP_ID
and tsu.DBID = snap.DBID
and snap.instance_number = 1
and tsu.TABLESPACE_ID = vt.TS#
and vt.NAME = dt.TABLESPACE_NAME
-- and dt.TABLESPACE_NAME='USERS' --包含特定表空间
and dt.TABLESPACE_NAME not in ('SYSTEM','SYSAUX')--不包括系统表空间和辅助表空间
and dt.contents not in ('TEMPORARY','UNDO') --不包括临时表空间和UNDO表空间
and SUBSTR(to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),12,5)='00:00'
group by snap.END_INTERVAL_TIME
order by snap.END_INTERVAL_TIME desc) a
order by day desc;


相关参考：
https://sqlora.blog.csdn.net/article/details/106069044
http://blog.itpub.net/24500180/viewspace-1062905/
http://blog.itpub.net/26736162/viewspace-2657751/