最近发现生产环境主要表空间增长过快，每天几个G，想查看一下原因。
查看快照信息：
select * from DBA_HIST_SNAPSHOT order by 1 desc
根据快照信息，查看历史端使用情况：
create table t1 as select * from DBA_HIST_SEG_STAT where snap_id=426
create table t2 as select * from DBA_HIST_SEG_STAT where snap_id=426+24;
统计使用情况：
select t1.obj#,
t1.dataobj#,
t2.SPACE_ALLOCATED_TOTAL - t1.SPACE_ALLOCATED_TOTAL
from t1, t2
where t1.obj# = t2.obj#
and t1.dataobj# = t2.dataobj#
order by 3 desc;
然后链接上DBA_HIST_SEG_STAT_OBJ表确定表名
稍后会根据这些情况查看相应表，从而定位业务。
查看数据文件的创建时间：
select file#,t.CREATION_TIME,t.NAME from v$datafile t order by 2 desc
查看快照信息：
select * from DBA_HIST_SNAPSHOT order by 1 desc
根据快照信息，查看历史端使用情况：
create table t1 as select * from DBA_HIST_SEG_STAT where snap_id=426
create table t2 as select * from DBA_HIST_SEG_STAT where snap_id=426+24;
统计使用情况：
select t1.obj#,
t1.dataobj#,
t2.SPACE_ALLOCATED_TOTAL - t1.SPACE_ALLOCATED_TOTAL
from t1, t2
where t1.obj# = t2.obj#
and t1.dataobj# = t2.dataobj#
order by 3 desc;
然后链接上DBA_HIST_SEG_STAT_OBJ表确定表名
select t1.obj#,
t1.dataobj#,
t2.SPACE_ALLOCATED_TOTAL - t1.SPACE_ALLOCATED_TOTAL,
a.owner
a.object_name
from t1, t2,DBA_HIST_SEG_STAT_OBJ a
where t1.obj# = t2.obj#
and t1.dataobj# = t2.dataobj#
and t1.obj#=a.obj#
and t1.dataobj#=a.dataobj#
order by 3 desc;
t1.dataobj#,
t2.SPACE_ALLOCATED_TOTAL - t1.SPACE_ALLOCATED_TOTAL,
a.owner
a.object_name
from t1, t2,DBA_HIST_SEG_STAT_OBJ a
where t1.obj# = t2.obj#
and t1.dataobj# = t2.dataobj#
and t1.obj#=a.obj#
and t1.dataobj#=a.dataobj#
order by 3 desc;
稍后会根据这些情况查看相应表，从而定位业务。
查看数据文件的创建时间：
select file#,t.CREATION_TIME,t.NAME from v$datafile t order by 2 desc
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29135257/viewspace-1774802/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
编译无效对象
下一篇：
关于java文件名字影响系统配置
请登录后发表评论
登录
全部评论
