小编给大家分享一下如何解决System表空间不足的报警问题，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

具体代码如下所示：

--SYSTEM表空间不足的报警登录之后，查询，发现是sys.aud$占的地方太多。SQL>selectowner,segment_name,segment_type,sum(bytes)/1024/1024space_mfromdba_segmentswheretablespace_name='SYSTEM'groupbyowner,segment_name,segment_typehavingsum(bytes)/1024/1024>=20orderbyspace_mdesc;4567OWNERSEGMENT_NAMESEGMENT_TYPESPACE_M----------------------------------------------SYSAUD$TABLE4480SYSIDL_UB1$TABLE272SYSSOURCE$TABLE72SYSIDL_UB2$TABLE32SYSC_OBJ#_INTCOL#CLUSTER27SYSC_TOID_VERSION#CLUSTER246rowsselected.SQL>查看是哪个记得比较多。coluserhostformata30selectuserid,userhost,count(1)fromsys.aud$wherentimestamp#>=CAST(to_date('2014-03-0100:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)groupbyuserid,userhosthavingcount(1)>500orderbycount(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-DDhh34: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-DDhh34:mi:ss')ASTIMESTAMP)andCAST(to_date('2014-03-1100:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)anduserid='xxxx'anduserhost='xxxx'groupbyspare1;selectaction#,count(1)fromsys.aud$wherentimestamp#betweenCAST(to_date('2014-03-1000:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)andCAST(to_date('2014-03-1100:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)anduserid='xxxx'anduserhost='xxxx'andspare1='xxxx'groupbyaction#orderbycount(1)desc;结果如下：ACTION#COUNT(1)--------------------101124043100124043SQL>其实是上次打开的audit一直没有关闭。关闭：SQL>noauditsession;清空：truncatetablesys.aud$;------------------------------------------------------------------------实战--------------------------------------------------------------------------1,查询表空间占用情况selectdbf.tablespace_nameastablespace_name,dbf.totalspaceastotalspace,dbf.totalblocksastotalblocks,dfs.freespacefreespace,dfs.freeblocksfreeblocks,(dfs.freespace/dbf.totalspace)*100asfreeRatefrom(selectt.tablespace_name,sum(t.bytes)/1024/1024totalspace,sum(t.blocks)totalblocksfromDBA_DATA_FILEStgroupbyt.tablespace_name)dbf,(selecttt.tablespace_name,sum(tt.bytes)/1024/1024freespace,sum(tt.blocks)freeblocksfromDBA_FREE_SPACEttgroupbytt.tablespace_name)dfswheretrim(dbf.tablespace_name)=trim(dfs.tablespace_name)--2,查看哪里占的比较多SYSTEM为step1中查询tablespace_name内容selectowner,segment_name,segment_type,sum(bytes)/1024/1024space_mfromdba_segmentswheretablespace_name='SYSTEM'groupbyowner,segment_name,segment_typehavingsum(bytes)/1024/1024>=20orderbyspace_mdesc--3,查看是哪个记得比较多count(1)越大,说明占得比较多selectuserid,userhost,count(1)fromsys.aud$wherentimestamp#>=CAST(to_date('2014-03-0100:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)groupbyuserid,userhosthavingcount(1)>500orderbycount(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-DDhh34: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-DDhh34:mi:ss')ASTIMESTAMP)andCAST(to_date('2016-12-1100:00:00','YYYY-MM-DDhh34: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-DDhh34:mi:ss')ASTIMESTAMP)andCAST(to_date('2016-12-1100:00:00','YYYY-MM-DDhh34:mi:ss')ASTIMESTAMP)anduserid='userid'anduserhost='userhost'andspare1='Administrator'groupbyaction#orderbycount(1)desc--5,关闭seeionnoauditsession;--6,清空：truncatetablesys.aud$;

以上是“如何解决System表空间不足的报警问题”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！