System表空间不足如何处置 怎样查表空间占用的情况

这篇文章给大家分享的是System表空间不足如何处理，怎样查表空间占用的情况。小编觉得挺实用的，因此分享给大家做个参考，文中的介绍得很详细，而要易于理解和学习，有需要的朋友可以参考，接下来就跟随小编一起了解看看吧。

废话不多说了，具体代码如下所示：

--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$;

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!