ORACLE故障处理：System表空间不足的报警问题

登录之后，查询，发现是sys.aud$占的地方太多。
SQL select owner, segment_name, segment_type, sum(bytes)/1024/1024 space_m
from dba_segments
where tablespace_name = SYSTEM
group by owner, segment_name, segment_type
having sum(bytes)/1024/1024 = 20
order by space_m desc
;
4 5 6 7
OWNER SEGMENT_NAME SEGMENT_TYPE SPACE_M
- -
SYS AUD$ TABLE 4480
SYS IDL_UB1$ TABLE 272
SYS SOURCE$ TABLE 72
SYS IDL_UB2$ TABLE 32
SYS C_OBJ#_INTCOL# CLUSTER 27
SYS C_TOID_VERSION# CLUSTER 24
6 rows selected.
SQL
查看是哪个记得比较多。
col userhost format a30
select userid, userhost, count(1) from sys.aud$
where ntimestamp# =CAST(to_date( 2014-03-01 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
group by userid, userhost
having count(1) 500
order by count(1) desc
;
再继续找哪天比较多。
select to_char(ntimestamp#, YYYY-MM-DD ) audit_date, count(1)
from sys.aud$
where ntimestamp# =CAST(to_date( 2014-03-01 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = xxxx and userhost = xxxx
group by to_char(ntimestamp#, YYYY-MM-DD )
order by count(1) desc
;
select spare1, count(1) from sys.aud$
where ntimestamp# between CAST(to_date( 2014-03-10 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and CAST(to_date( 2014-03-11 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = xxxx and userhost = xxxx
group by spare1
;
select action#, count(1) from sys.aud$
where ntimestamp# between CAST(to_date( 2014-03-10 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and CAST(to_date( 2014-03-11 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = xxxx and userhost = xxxx
and spare1 = xxxx
group by action#
order by count(1) desc
;
结果如下：
ACTION# COUNT(1)
- -
101 124043
100 124043
SQL
其实是上次打开的audit一直没有关闭。
关闭：
SQL noaudit session;
清空：
truncate table sys.aud$;

实战

1,查询表空间占用情况
select dbf.tablespace_name as tablespace_name,
dbf.totalspace as totalspace,
dbf.totalblocks as totalblocks,
dfs.freespace freespace,
dfs.freeblocks freeblocks,
(dfs.freespace / dbf.totalspace) * 100 as freeRate
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from DBA_DATA_FILES t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from DBA_FREE_SPACE tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
2,查看哪里占的比较多 SYSTEM 为step1中查询 tablespace_name 内容
select owner, segment_name, segment_type, sum(bytes)/1024/1024 space_m
from dba_segments
where tablespace_name = SYSTEM
group by owner, segment_name, segment_type
having sum(bytes)/1024/1024 = 20
order by space_m desc
3,查看是哪个记得比较多 count(1) 越大,说明占得比较多
select userid, userhost, count(1) from sys.aud$
where ntimestamp# =CAST(to_date( 2014-03-01 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
group by userid, userhost
having count(1) 500
order by count(1) desc
4,再继续找哪天比较多 userid userhost 为上一步查询内容
select to_char(ntimestamp#, YYYY-MM-DD ) audit_date, count(1)
from sys.aud$
where ntimestamp# =CAST(to_date( 2015-03-01 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = userid and userhost = userhost
group by to_char(ntimestamp#, YYYY-MM-DD )
order by count(1) desc
;
select spare1, count(1) from sys.aud$
where ntimestamp# between CAST(to_date( 2016-03-10 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and CAST(to_date( 2016-12-11 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = userid and userhost = userhost
group by spare1
;
spare1 为上一步查询内容
select action#, count(1) from sys.aud$
where ntimestamp# between CAST(to_date( 2016-03-10 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and CAST(to_date( 2016-12-11 00:00:00 , YYYY-MM-DD hh24:mi:ss ) AS TIMESTAMP)
and userid = userid and userhost = userhost
and spare1 = Administrator
group by action#
order by count(1) desc
5,关闭seeion
noaudit session;
6,清空：
truncate table sys.aud$;

以上所述是小编给大家介绍的System表空间不足的报警，大家如有疑问可以留言，或者联系站长。感谢亲们支持！！！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 ORACLE故障处理：System表空间不足的报警问题