查看表空间的增长情况

--查询前一周表空间增长情况
select c.tablespace_name,
d."total(mb)",
d."used(mb)" - c."used(mb)" as "increment(mb)",
to_char(next_day(trunc(sysdate), 2) - 7, 'yyyy/mm/dd') || '--' ||
to_char(next_day(trunc(sysdate), 2) - 7, 'yyyy/mm/dd') "time"
from (select b.name tablespace_name,
case
when b.name not like 'undo%' then
round(a.tablespace_size * 8 / 1024)
when b.name like 'undo%' then
round(a.tablespace_size * 8 / 1024 / 2)
end as "total(mb)",
round(a.tablespace_usedsize * 8 / 1024) "used(mb)",
a.rtime
from dba_hist_tbspc_space_usage a, v$tablespace b
where a.tablespace_id = b.ts#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(next_day(trunc(sysdate), 2) - 14, 'yyyymmdd hh24:mi')) c,
(select b.name tablespace_name,
case
when b.name not like 'undo%' then
round(a.tablespace_size * 8 / 1024)
when b.name like 'undo%' then
round(a.tablespace_size * 8 / 1024 / 2)
end as "total(mb)",
round(a.tablespace_usedsize * 8 / 1024) "used(mb)",
a.rtime
from dba_hist_tbspc_space_usage a, v$tablespace b
where a.tablespace_id = b.ts#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(next_day(trunc(sysdate), 2) - 7, 'yyyymmdd hh24:mi')) d
where c.tablespace_name = d.tablespace_name;

--查询前一天表空间增长情况

select c.tablespace_name,
d."total(mb)",
d."used(mb)" - c."used(mb)" as "increment(mb)",
to_char(trunc(sysdate - 1), 'yyyy/mm/dd') "time"
from (select b.name tablespace_name,
case
when b.name not like 'undo%' then
round(a.tablespace_size * 8 / 1024)
when b.name like 'undo%' then
round(a.tablespace_size * 8 / 1024 / 2)
end as "total(mb)",
round(a.tablespace_usedsize * 8 / 1024) "used(mb)",
a.rtime
from dba_hist_tbspc_space_usage a, v$tablespace b
where a.tablespace_id = b.ts#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) c,
(select b.name tablespace_name,
case
when b.name not like 'undo%' then
round(a.tablespace_size * 8 / 1024)
when b.name like 'undo%' then
round(a.tablespace_size * 8 / 1024 / 2)
end as "total(mb)",
round(a.tablespace_usedsize * 8 / 1024) "used(mb)",
a.rtime
from dba_hist_tbspc_space_usage a, v$tablespace b
where a.tablespace_id = b.ts#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) d
where c.tablespace_name = d.tablespace_name;
--在得知哪个表空间增长明显的情况下，再去查询该表空间的对象大小情况，tablespace_name为某表空间名称
select owner, segment_name, segment_type, bytes / 1024 / 1024 / 1024 gb
from dba_segments
where tablespace_name ='&tablespace_name'
order by bytes desc;

select *
from (select owner,
segment_name,
segment_type,
bytes / 1024 / 1024 / 1024 gb
from dba_segments
where tablespace_name ='&tablespace_name'
order by bytes desc)
where rownum < 30;
--如何确定某些对象容量增涨快，需要一定时间的跟踪，周期性查询该对象的容量。然后根据对象做相应后续处理。