重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle查询1个月内数据增长情况 [打印本页]

作者: jiawang 时间: 2024-8-15 16:25
标题: Oracle查询1个月内数据增长情况

ORACLE 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, 2) ts_size_mb,
round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
2) ts_free_mb,
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;

Oracle 10g 和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, 2) ts_size_mb,
round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
2) ts_free_mb,
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;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)