表空间每日增长量统计
11g
SELECT a.snap_id,
c.tablespace_name ts_name,
TO_CHAR (TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss'),
'yyyy-mm-dd hh24:mi')
rtime,
ROUND (a.tablespace_size * c.block_size / 1024 / 1024 / 1024, 2)
ts_size_gb,
ROUND (a.tablespace_usedsize * c.block_size / 1024 / 1024 / 1024, 2)
ts_used_gb,
ROUND (
(a.tablespace_size - a.tablespace_usedsize)
* c.block_size
/ 1024
/ 1024
/ 1024,
2)
ts_free_gb,
ROUND (a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
FROM dba_hist_tbspc_space_usage a,
( SELECT tablespace_id,
SUBSTR (rtime, 1, 10) rtime,
MAX (snap_id) snap_id
FROM dba_hist_tbspc_space_usage nb
GROUP BY tablespace_id, SUBSTR (rtime, 1, 10)) b,
dba_tablespaces c,
v$tablespace d
WHERE a.snap_id = b.snap_id
AND a.tablespace_id = b.tablespace_id
AND a.tablespace_id = d.TS#
AND d.NAME = c.tablespace_name
AND TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') >= SYSDATE - 30
ORDER BY a.tablespace_id, TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') DESC;
12c
--针对多租户数据库情况
SELECT a.snap_id,
a.con_id,
e.name pdbname,
c.tablespace_name ts_name,
TO_CHAR (TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss'),
'yyyy-mm-dd hh24:mi')
rtime,
ROUND (a.tablespace_size * c.block_size / 1024 / 1024 / 1024, 2)
ts_size_gb,
ROUND (a.tablespace_usedsize * c.block_size / 1024 / 1024 / 1024, 2)
ts_used_gb,
ROUND (
(a.tablespace_size - a.tablespace_usedsize)
* c.block_size
/ 1024
/ 1024
/ 1024,
2)
ts_free_gb,
ROUND (a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
FROM cdb_hist_tbspc_space_usage a,
( SELECT tablespace_id,
nb.con_id,
SUBSTR (rtime, 1, 10) rtime,
MAX (snap_id) snap_id
FROM dba_hist_tbspc_space_usage nb
GROUP BY tablespace_id, nb.con_id, SUBSTR (rtime, 1, 10)) b,
cdb_tablespaces c,
v$tablespace d,
V$CONTAINERS e
WHERE a.snap_id = b.snap_id
AND a.tablespace_id = b.tablespace_id
AND a.con_id = b.con_id
AND a.con_id = c.con_id
AND a.con_id = d.con_id
AND a.con_id = e.con_id
AND a.tablespace_id = d.TS#
AND d.NAME = c.tablespace_name
AND TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') >= SYSDATE - 30
ORDER BY a.CON_ID,
a.tablespace_id,
TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') DESC;
11g某一个表空间增长量
SELECT u.snap_id,
TO_CHAR (s.begin_interval_time, 'yyyy-mm-dd hh24') begin_time,
TO_CHAR (s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
t.name,
ROUND (u.tablespace_size * ts.block_size / 1024 / 1024 / 1024, 2)
ts_size_gb,
ROUND (u.tablespace_usedsize * ts.block_size / 1024 / 1024 / 1024, 2)
ts_used_gb,
ROUND (
(u.tablespace_size - u.tablespace_usedsize)
* ts.block_size
/ 1024
/ 1024
/ 1024,
2)
ts_free_gb,
ROUND (u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
FROM dba_hist_tbspc_space_usage u,
v$tablespace t,
dba_hist_snapshot s,
dba_tablespaces ts
WHERE u.tablespace_id = t.ts#
AND u.snap_id = s.snap_id
AND t.name = ts.tablespace_name
AND s.instance_number = 1
AND t.name = 'SYSTEM'
AND s.end_interval_time > SYSDATE - 7
ORDER BY snap_id DESC;
数据库每日增长量统计
根据表空间计算
/* 不含UNDO和TEMP表空间 */
WITH tmp
AS ( SELECT rtime,
SUM (tablespace_usedsize_gb) tablespace_usedsize_gb,
SUM (tablespace_size_gb) tablespace_size_gb
FROM (SELECT rtime,
e.tablespace_id,
(e.tablespace_usedsize)
* (f.block_size)
/ 1024
/ 1024
/ 1024
tablespace_usedsize_gb,
(e.tablespace_size)
* (f.block_size)
/ 1024
/ 1024
/ 1024
tablespace_size_gb
FROM dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
WHERE e.tablespace_id = g.TS#
AND f.tablespace_name = g.NAME
AND f.contents NOT IN ('TEMPORARY', 'UNDO'))
GROUP BY rtime)
SELECT tmp.rtime,
tablespace_usedsize_gb,
tablespace_size_gb,
(tablespace_usedsize_gb
- LAG (tablespace_usedsize_gb, 1, NULL) OVER (ORDER BY tmp.rtime))
AS DIFF_GB
FROM tmp,
( SELECT MAX (rtime) rtime
FROM tmp
GROUP BY SUBSTR (rtime, 1, 10)) t2
WHERE t2.rtime = tmp.rtime;
/* 含UNDO和TEMP表空间 */
WITH tmp
AS ( SELECT MIN (rtime) rtime,
SUM (tablespace_usedsize_gb) tablespace_usedsize_gb,
SUM (tablespace_size_gb) tablespace_size_gb
FROM (SELECT rtime,
e.tablespace_id,
(e.tablespace_usedsize)
* (f.block_size)
/ 1024
/ 1024
/ 1024
tablespace_usedsize_gb,
(e.tablespace_size)
* (f.block_size)
/ 1024
/ 1024
/ 1024
tablespace_size_gb
FROM dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
WHERE e.tablespace_id = g.TS#
AND f.tablespace_name = g.NAME)
GROUP BY rtime)
SELECT tmp.rtime,
tablespace_usedsize_gb,
tablespace_size_gb,
(tablespace_usedsize_gb
- LAG (tablespace_usedsize_gb, 1, NULL) OVER (ORDER BY tmp.rtime))
AS DIFF_KB
FROM tmp,
( SELECT MIN (rtime) rtime
FROM tmp
GROUP BY SUBSTR (rtime, 1, 10)) t2
WHERE t2.rtime = tmp.rtime
参考：DB宝Oracle查询表空间或数据库的增长量
