开发人员通过plsql登录或者操作数据库,提示如下错误:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
解决方法:
根据提示很明显是system空间无法扩展,审计记录无法写入,表面上是system表空间不足,其实不然,从错误信息可以看出,该环境审计功能是开启的,这种情况大多数都是由于审计信息太多,导致系统表空间紧张导致的,清空审计信息就可以解决,此处就是由于审计信息记录太大导致的,如果没有审计需求,可以关闭审计功能。以下是清理审计的方法:
先通过以下语句查出审计表占用的空间大小:
1234567891011121314151617181920212223242526272829303132333435363738
select
owner,
table_name,
sum
(decode(seg_type,
'table'
, size_mb))
"数据段大小"
,
sum
(decode(seg_type,
'index'
, size_mb))
"索引段大小"
,
sum
(decode(seg_type,
'lob'
, size_mb))
"大字段大小"
,
sum
(size_mb)
"表总大小"
from
(
---所有表大小
select
t.owner,
t.table_name,
sum
(s.bytes) / 1024 / 1024 size_mb,
'table'
seg_type
from
dba_segments s, dba_tables t
where
s.owner = t.owner
and
s.segment_name = t.table_name
group
by
t.owner, t.table_name
union
all
---大字段大小
select
l.owner,
l.table_name,
sum
(s.bytes) / 1024 / 1024 size_mb,
'lob'
seg_type
from
dba_segments s, dba_lobs l
where
s.owner = l.owner
and
s.segment_name = l.segment_name
group
by
l.owner, l.table_name
union
all
---索引段大小
select
i.table_owner,
i.table_name,
sum
(s.bytes) / 1024 / 1024 size_mb,
'index'
seg_type
from
dba_segments s, dba_indexes i
where
s.owner = i.owner
and
s.segment_name = i.index_name
group
by
i.table_owner, i.table_name)
where
owner =
'SYS'
and
table_name=
'AUD$'
group
by
owner, table_name;
根据查出的审计表的大小分段释放空间,如查出来大小10G
,分5段释放
,逐次将审计表中的数据清除,使之逐步释放空间,过程语句如下:
123456
TRUNCATE
TABLE
sys.aud$ reuse storage;
ALTER
TABLE
sys.aud$
deallocate
unused keep 10800M;
ALTER
TABLE
sys.aud$
deallocate
unused keep 6144M;
ALTER
TABLE
sys.aud$
deallocate
unused keep 4096M;
ALTER
TABLE
sys.aud$
deallocate
unused keep 2048M;
ALTER
TABLE
sys.aud$
deallocate
unused keep 0M;