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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。