当前位置： 移动技术网 > IT编程>数据库>Oracle > 查看表空间的增长情况

查看表空间的增长情况

2019年05月29日  | 移动技术网IT编程  | 我要评论

--查询前一周表空间增长情况
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;
--如何确定某些对象容量增涨快，需要一定时间的跟踪，周期性查询该对象的容量。然后根据对象做相应后续处理。

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网