—查看表历史情况脚本，检查过去4天表的变化情况（增长情况）
SELECT *
FROM ( SELECT c.TABLESPACE_NAME,
c.segment_name,
b.object_type,
ROUND (SUM (space_used_delta) / 1024 / 1024, 2) “Growth (MB)”
FROM dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE begin_interval_time > TRUNC (SYSDATE) - &days_back
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.object_name = c.segment_name
AND c.owner = ‘&SCHEMANAME’
GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)
ORDER BY 1,4 ASC;
—监控每个表空间的变化量：可以通过dba_hist_tbspc_space_usage查看增长情况，根据快速增长的时间，然后dba_segment按照大小分组查看是够有某几个对象一直增长，通过session或ash视图对应logon_time的会话与执行sql_id在做的操作
SELECT TO_CHAR (sp.begin_interval_time,‘YYYY-MM-DD’) days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(10241024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN (‘SYSAUX’,‘SYSTEM’)
–AND TS.tsname=‘SYNERGY_TRANSFUSION’
GROUP BY TO_CHAR (sp.begin_interval_time,‘YYYY-MM-DD’), ts.tsname
ORDER BY days ;
–输入tablespace_name，分析7天内变化量
select sample_time,
size_gb - lag(size_gb, 1) over(order by size_gb asc) as increment_gb
from (select to_char(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’) - 1,
‘yyyy-mm-dd’) sample_time,
round(tablespace_usedsize * 8 / 1024 / 1024, 2) size_gb,
row_number() over(partition by to_char(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’), ‘yyyy-mm-dd’) order by snap_id) rn
from dba_hist_tbspc_space_usage t, v$tablespace s
where t.tablespace_id = s.ts#
and s.name = ‘&tablespace_name’
and to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’) > sysdate - 7
order by t.snap_id)
where rn = 1
order by sample_time;
select a.name, b.*
from v$tablespace a,
(select tablespace_id,
trunc(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’)) datetime,
round(max(tablespace_usedsize * 8 / 1024 /1024), 2) used_size_GB
from dba_hist_tbspc_space_usage
where trunc(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’)) >
trunc(sysdate - 7)
group by tablespace_id,
trunc(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’))
order by tablespace_id,
trunc(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’))) b
where a.ts# = b.tablespace_id
and a.name = ‘USERS’;
–每个时间段，具体变化量
select s.name tablespace_name,
to_char(to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’),
‘yyyy-mm-dd hh24:mi’) sample_time,
round(tablespace_size * 8 / 1024 / 1024, 2) tablespace_size_gb,
round(tablespace_usedsize * 8 / 1024 / 1024, 2) tablespace_usedsize_gb,
round(tablespace_usedsize * 100 / tablespace_size, 2) tbs_usage
from dba_hist_tbspc_space_usage t, v$tablespace s
where t.tablespace_id = s.ts#
and s.name = ‘&tablespace_name’
and to_date(rtime, ‘mm/dd/yyyy hh24:mi:ss’) > sysdate - 7
order by t.snap_id;
欢迎关注个人微信公众号，获取更多知识
