How to solve the alarm problem of insufficient System table space
Editor to share with you how to solve the alarm problem of insufficient System table space, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
The specific code is as follows:
-- after logging in to the alarm when the SYSTEM table space is insufficient, the query found that sys.aud$ takes up too much space. SQL > select owner, segment_name, segment_type, sum (bytes) / 1024 order by space_m desc 1024 space_m from dba_segments where tablespace_name = 'SYSTEM' group by owner, segment_name, segment_type having sum (bytes) / 1024 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 > see which one remembers more. Col userhost format a30 select userid, userhost, count (1) from sys.aud$ where ntimestamp# > = CAST (to_date ('2014-03-01 00 order by count) AS TIMESTAMP) group by userid, userhost having count (1) > 500 order by count (1) desc; keep looking for more days. Select to_char (ntimestamp#, 'YYYY-MM-DD') audit_date, count (1) from sys.aud$ where ntimestamp# > = CAST (to_date (' 2014-03-01 00 YYYY-MM-DD hh34: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-100 YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and CAST (to_date (' 2014-03-11 00 from sys.aud$ where ntimestamp# between CAST) 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 YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and CAST (to_date (' 2014-03-11 00 V) 00V, 'YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and userid =' xxxx' and userhost = 'xxxx' and spare1 =' xxxx' group by action# order by count (1) desc The result is as follows: ACTION# COUNT (1)-10110100 124043 124043 SQL > is actually the last time the audit was opened and not closed. Close: SQL > noaudit session; clear: truncate table sys.aud$ -actual combat -1 Query table space occupancy 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 to see which accounts for more SYSTEM query tablespace_name content select owner, segment_name, segment_type in step1 Sum (bytes) / 1024 userhost 1024 space_m from dba_segments where tablespace_name = 'SYSTEM' group by owner, segment_name, segment_type having sum (bytes) / 1024 order by space_m desc > = 20 order by space_m desc-- 3 to see which remembers more count (1) larger, indicating more select userid, userhost, count (1) from sys.aud$ where ntimestamp# > = CAST (to_date (' 2014-03-01 00space_m from dba_segments where tablespace_name) 'YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) group by userid, userhost having count (1) > 500order by count (1) desc-- 4, and then continue to find which day to compare more userid userhost for the previous query content select to_char (ntimestamp#,' YYYY-MM-DD') audit_date, count (1) from sys.aud$ where ntimestamp# > = CAST (to_date ('2015-03-01 00 order by count) 'YYYY-MM-DD hh34: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-100 YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and CAST (to_date (' 2016-12-11 00 and userid) AS TIMESTAMP) and userid = 'userid' and userhost =' userhost' group by spare1 -- spare1 queries the contents of select action#, count (1) from sys.aud$ where ntimestamp# between CAST (to_date ('2016-03-100 YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and CAST (to_date (' 2016-12-11 00 from sys.aud$ where ntimestamp# between CAST) for the previous step) 'YYYY-MM-DD hh34:mi:ss') AS TIMESTAMP) and userid =' userid' and userhost = 'userhost' and spare1 =' Administrator' group by action# order by count (1) desc-- 5, close seeion noaudit session -- 6, clear: truncate table sys.aud$; is all the contents of this article entitled "how to solve the alarm problem of insufficient System tablespace". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
