Analysis of insufficient System tablespace alarms and analysis of system table alarms

Analysis of insufficient System tablespace alarms and analysis of system table alarms

The Code is as follows:

-- After logging on to an alert with insufficient SYSTEM tablespace, the SYSTEM finds that sys. aud $ occupies too many places. 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 -------- orders ------- 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> check which one remembers more. Col userhost format a30 select userid, userhost, count (1) from sys. aud $ where ntimestamp #> = CAST (to_date ('2017-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; continue to find more days. Select to_char (ntimestamp #, 'yyyy-MM-DD ') audit_date, count (1) from sys. aud $ where ntimestamp #> = CAST (to_date ('2017-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 ('2017-03-10 00:00:00 ', 'yyyy-MM-DD hh24: Mi: ss') as timestamp) and CAST (to_date ('2017-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 ('2017-03-10 00:00:00 ', 'yyyy-MM-DD hh24: mi: ss') as timestamp) and CAST (to_date ('2017-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; Result: ACTION # COUNT (1) ---------- 101 124043 100 124043 SQL> In fact, the last opened audit has not been closed. Close: SQL> noaudit session; clear: truncate table sys. aud $; explain practice ------------------------------------------------------------------------ -- 1. query the table space usage select dbf. tablespace_name as tablespace_name, dbf. totalspace as totalspace, dbf. totalblocks as totalblocks, dfs. freespace, dfs. 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, check where most of the tables are occupied. in step 1, the SYSTEM queries tablespace_name content 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, check which one remembers that the larger count (1) is, the more select userid, userhost, count (1) from sys. aud $ where ntimestamp #> = CAST (to_date ('2017-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, continue to find the day when more userid userhost is the content of the previous query select to_char (ntimestamp #, 'yyyy-MM-DD ') audit_date, count (1) from sys. aud $ where ntimestamp #> = CAST (to_date ('2017-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 ('2017-03-10 00:00:00 ', 'yyyy-MM-DD hh24: mi: ss') as timestamp) and CAST (to_date ('2017-12-11 00:00:00 ', 'yyyy-MM-DD hh24: mi: ss') as timestamp) and userid = 'userid' and userhost = 'userhost' group by spare1; -- spare1 indicates the select action #, count (1) from sys. aud $ where ntimestamp # between CAST (to_date ('2017-03-10 00:00:00 ', 'yyyy-MM-DD hh24: mi: ss') as timestamp) and CAST (to_date ('2017-12-11 00:00:00 ', 'yyyy-MM-DD hh24: mi: ss') as timestamp) and userid = 'userid' and userhost = 'userhost' and spare1 = 'admin' group by action # order by count (1) desc -- 5, disable seeion noaudit session; -- 6, clear: truncate table sys. aud $;

Summary

The above is a small Editor to introduce you to the System tablespace insufficient alarm, hope to help you, if you have any questions, please leave a message, the small editor will reply to you in a timely manner. Thank you very much for your support for the help House website!