System表空间不足的报警问题浅析

这篇文章主要介绍了System表空间不足的报警问题浅析,需要的朋友可以参考下

废话不多说了,具体代码如下所示:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

–SYSTEM表空间不足的报警

登录之后,查询,发现是sys.aud$占的地方太多。

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

——– ——————————- ——-

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>

查看是哪个记得比较多。

col userhost format a30

select userid, userhost, count(1) from sys.aud$

where ntimestamp# >=CAST(to_date('2014-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

;

再继续找哪天比较多。

select to_char(ntimestamp#, 'YYYY-MM-DD') audit_date, count(1)

from sys.aud$

where ntimestamp# >=CAST(to_date('2014-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('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and CAST(to_date('2014-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('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and CAST(to_date('2014-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

;

结果如下:

ACTION# COUNT(1)

———- ———-

101 124043

100 124043

SQL>

其实是上次打开的audit一直没有关闭。

关闭:

SQL> noaudit session;

清空:

truncate table sys.aud$;

————————————————————————

实战

————————————————————————

–1,查询表空间占用情况

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,查看哪里占的比较多 SYSTEM 为step1中查询 tablespace_name 内容

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,查看是哪个记得比较多 count(1) 越大,说明占得比较多

select userid, userhost, count(1) from sys.aud$

where ntimestamp# >=CAST(to_date('2014-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,再继续找哪天比较多 userid userhost 为上一步查询内容

select to_char(ntimestamp#, 'YYYY-MM-DD') audit_date, count(1)

from sys.aud$

where ntimestamp# >=CAST(to_date('2015-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('2016-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and CAST(to_date('2016-12-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and userid = 'userid' and userhost = 'userhost'

group by spare1

;

–spare1 为上一步查询内容

select action#, count(1) from sys.aud$

where ntimestamp# between CAST(to_date('2016-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and CAST(to_date('2016-12-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)

and userid = 'userid' and userhost = 'userhost'

and spare1 = 'Administrator'

group by action#

order by count(1) desc

–5,关闭seeion

noaudit session;

–6,清空:

truncate table sys.aud$;

总结

以上所述是小编给大家介绍的System表空间不足的报警,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对钦钦技术栈网站的支持!

原文链接:http://blog.csdn.net/woshimyc/article/details/78280423

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24997/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月23日 下午12:19
下一篇 2022年9月23日 下午12:19
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!