查看表空间的历史增长情况来计算数据库历史情况。
WITH tmp
AS ( SELECT rtime,
SUM (tablespace_usedsize_mb) tablespace_usedsize_mb,
SUM (tablespace_size_mb) tablespace_size_mb
FROM (SELECT rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024/1024
tablespace_usedsize_mb,
(e.tablespace_size) * (f.block_size) / 1024/1024
tablespace_size_mb
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') ---不含undo和temp
--and f.tablespace_name='user' --user表空间
)
GROUP BY rtime)
SELECT tmp.rtime,
tablespace_usedsize_mb,
tablespace_size_mb,
( tablespace_usedsize_mb
- LAG (tablespace_usedsize_mb, 1, NULL) OVER (ORDER BY tmp.rtime))
AS DIFF_mB
FROM tmp,
( SELECT MAX (rtime) rtime
FROM tmp
GROUP BY SUBSTR (rtime, 1, 10)) t2
WHERE t2.rtime = tmp.rtime;
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;
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;
WITH tmp
AS ( SELECT rtime,
SUM (tablespace_usedsize_mb) tablespace_usedsize_mb,
SUM (tablespace_size_mb) tablespace_size_mb
FROM (SELECT rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024/1024
tablespace_usedsize_mb,
(e.tablespace_size) * (f.block_size) / 1024/1024
tablespace_size_mb
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') ---不含undo和temp
---and f.tablespace_name='user'
)
GROUP BY rtime)
SELECT tmp.rtime,
tablespace_usedsize_mb,
tablespace_size_mb,
( tablespace_usedsize_mb
- LAG (tablespace_usedsize_mb, 1, NULL) OVER (ORDER BY tmp.rtime))
AS DIFF_mB
FROM tmp,
( SELECT MAX (rtime) rtime
FROM tmp
GROUP BY SUBSTR (rtime, 1, 10)) t2
WHERE t2.rtime = tmp.rtime;
估算所有对象某段时间内数据块增长情况
SELECT obj.owner,
obj.object_name,
TO_CHAR (sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd') start_day,
SUM (a.db_block_changes_delta) block_increase
FROM dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
WHERE sn.snap_id = a.snap_id
AND obj.object_id = a.obj#
AND obj.owner NOT IN ('SYS', 'SYSTEM')
AND end_interval_time BETWEEN TO_TIMESTAMP ('2022-05-29',
'yyyy-mm-dd')
AND TO_TIMESTAMP ('2022-06-02',
'yyyy-mm-dd')
GROUP BY obj.owner,
obj.object_name,
TO_CHAR (sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd')
ORDER BY obj.owner, obj.object_name,3 desc;
估算某用户下对象，某段时间内数据块增长情况
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd') start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner in (upper('userA')) ---owner
and obj.object_name = upper('tableA') ---object
AND end_interval_time BETWEEN TO_TIMESTAMP ('2022-05-29',
'yyyy-mm-dd')
AND TO_TIMESTAMP ('2022-06-02',
'yyyy-mm-dd')
group by obj.owner,
obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd')
order by obj.owner, obj.object_name,3 desc;
根据DBA_TAB_MODIFICATIONS统计表上增删改次数
Database Reference对DBA_TAB_MODIFICATIONS解释是，记录了自从上一次收集统计信息后，表上数据被修改的记录。所以，使用的时候需要先查询下上次收集统计信息是啥时候：
select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name=upper('table_name');
set linesize 200
col TABLE_OWNER for a20
col TABLE_NAME for a26
col TIMESTAMP for a16
col count(INSERTS) for 9999999
col count(UPDATES) for 9999999
col count(DELETES) for 9999999
select TABLE_OWNER,TABLE_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24') TIMESTAMP,count(INSERTS),count(UPDATES),count(DELETES)
from DBA_TAB_MODIFICATIONS
where TABLE_NAME=upper('table_name')
group by TABLE_OWNER,TABLE_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24')
order by TIMESTAMP desc;
参考：
https://blog.csdn.net/u010033674/article/details/101290622?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-1-101290622-blog-112649876.pc_relevant_aa&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-1-101290622-blog-112649876.pc_relevant_aa&utm_relevant_index=2
