System表空间不足的报警问题浅析
废话不多说了，具体代码如下所示：
--SYSTEM表空间不足的报警
登录之后，查询，发现是sys.aud$占的地方太多。
SQL>selectowner,segment_name,segment_type,sum(bytes)/1024/1024space_m
fromdba_segments
wheretablespace_name='SYSTEM'
groupbyowner,segment_name,segment_type
havingsum(bytes)/1024/1024>=20
orderbyspace_mdesc
;
4567
OWNERSEGMENT_NAMESEGMENT_TYPESPACE_M
----------------------------------------------
SYSAUD$TABLE4480
SYSIDL_UB1$TABLE272
SYSSOURCE$TABLE72
SYSIDL_UB2$TABLE32
SYSC_OBJ#_INTCOL#CLUSTER27
SYSC_TOID_VERSION#CLUSTER24
6rowsselected.
SQL>
查看是哪个记得比较多。
coluserhostformata30
selectuserid,userhost,count(1)fromsys.aud$
wherentimestamp#>=CAST(to_date('2014-03-0100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
groupbyuserid,userhost
havingcount(1)>500
orderbycount(1)desc
;
再继续找哪天比较多。
selectto_char(ntimestamp#,'YYYY-MM-DD')audit_date,count(1)
fromsys.aud$
wherentimestamp#>=CAST(to_date('2014-03-0100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='xxxx'anduserhost='xxxx'
groupbyto_char(ntimestamp#,'YYYY-MM-DD')
orderbycount(1)desc
;
selectspare1,count(1)fromsys.aud$
wherentimestamp#betweenCAST(to_date('2014-03-1000:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
andCAST(to_date('2014-03-1100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='xxxx'anduserhost='xxxx'
groupbyspare1
;
selectaction#,count(1)fromsys.aud$
wherentimestamp#betweenCAST(to_date('2014-03-1000:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
andCAST(to_date('2014-03-1100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='xxxx'anduserhost='xxxx'
andspare1='xxxx'
groupbyaction#
orderbycount(1)desc
;
结果如下：
ACTION#COUNT(1)
--------------------
101124043
100124043
SQL>
其实是上次打开的audit一直没有关闭。
关闭：
SQL>noauditsession;
清空：
truncatetablesys.aud$;
------------------------------------------------------------------------
实战
------------------------------------------------------------------------
--1,查询表空间占用情况
selectdbf.tablespace_nameastablespace_name,
dbf.totalspaceastotalspace,
dbf.totalblocksastotalblocks,
dfs.freespacefreespace,
dfs.freeblocksfreeblocks,
(dfs.freespace/dbf.totalspace)*100asfreeRate
from(selectt.tablespace_name,
sum(t.bytes)/1024/1024totalspace,
sum(t.blocks)totalblocks
fromDBA_DATA_FILESt
groupbyt.tablespace_name)dbf,
(selecttt.tablespace_name,
sum(tt.bytes)/1024/1024freespace,
sum(tt.blocks)freeblocks
fromDBA_FREE_SPACEtt
groupbytt.tablespace_name)dfs
wheretrim(dbf.tablespace_name)=trim(dfs.tablespace_name)
--2,查看哪里占的比较多SYSTEM为step1中查询tablespace_name内容
selectowner,segment_name,segment_type,sum(bytes)/1024/1024space_m
fromdba_segments
wheretablespace_name='SYSTEM'
groupbyowner,segment_name,segment_type
havingsum(bytes)/1024/1024>=20
orderbyspace_mdesc
--3,查看是哪个记得比较多count(1)越大,说明占得比较多
selectuserid,userhost,count(1)fromsys.aud$
wherentimestamp#>=CAST(to_date('2014-03-0100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
groupbyuserid,userhost
havingcount(1)>500
orderbycount(1)desc
--4,再继续找哪天比较多useriduserhost为上一步查询内容
selectto_char(ntimestamp#,'YYYY-MM-DD')audit_date,count(1)
fromsys.aud$
wherentimestamp#>=CAST(to_date('2015-03-0100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='userid'anduserhost='userhost'
groupbyto_char(ntimestamp#,'YYYY-MM-DD')
orderbycount(1)desc
;
selectspare1,count(1)fromsys.aud$
wherentimestamp#betweenCAST(to_date('2016-03-1000:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
andCAST(to_date('2016-12-1100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='userid'anduserhost='userhost'
groupbyspare1
;
--spare1为上一步查询内容
selectaction#,count(1)fromsys.aud$
wherentimestamp#betweenCAST(to_date('2016-03-1000:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
andCAST(to_date('2016-12-1100:00:00','YYYY-MM-DDhh24:mi:ss')ASTIMESTAMP)
anduserid='userid'anduserhost='userhost'
andspare1='Administrator'
groupbyaction#
orderbycount(1)desc
--5,关闭seeion
noauditsession;
--6,清空：
truncatetablesys.aud$;
总结
以上所述是小编给大家介绍的System表空间不足的报警，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。