oracle 审计变换表空间_oracle rac 下oracle审计写到SYStem表空间了，system 空间满了，怎么移除来？...

前一段时间遇见了同样的问题。环境是11.2.0.4 rac,这是处理的过程,

---------仅供参考---------。

//临时解决方案：扩大system表空间

alter tablespace system add datafile '+data' size 100m autoextend on next 16m maxsize 20G;

然后用EXP导出目前的SYS.AUD$表

exp system/密码 file=audit.dmp log=audit.log tables=sys.aud$

//查询AUD$表大小

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 MB from dba_segments where SEGMENT_TYPE='TABLE' and SEGMENT_NAME='AUD$';

//查询AUD$表归属表空间

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME

FROM DBA_TABLES

WHERE TABLE_NAME = 'AUD$'

AND OWNER = 'SYS';

//截断AUD$表 (注意一旦截断后，审计表将被清空。)

truncate table sys.aud$ reuse storage;

//释放sys.AUD$表空间

alter table sys.aud$ deallocate unused keep 30000m;

alter table sys.aud$ deallocate unused keep 25000m;

alter table sys.aud$ deallocate unused keep 20000m;

alter table sys.aud$ deallocate unused keep 15000m;

alter table sys.aud$ deallocate unused keep 10000m;

alter table sys.aud$ deallocate unused keep 5000m;

alter table sys.aud$ deallocate unused keep 2500m;

alter table sys.aud$ deallocate unused keep 1000m;

alter table sys.aud$ deallocate unused keep 500m;

//创建用于放置AUD$表的一般表空间:AUD_FILE

create tablespace AUD_FILE datafile '+data' size 100M autoextend on next 16M maxsize 20G;

//将sys.AUD$迁移到AUD_FILE.AUD$

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

AUDIT_TRAIL_LOCATION_VALUE => 'AUD_FILE');

END;

/

//查询当前SYSTEM表空间各个字段占用情况

select from (select table_name,blocks8192/1024/1024/1024 GB from user_tables where blocks is not null order by 2 desc)where rownum<10;

//查询当前SYSTEM表空间占用情况及空间大小

select b.tablespace_name "tablespace",b.bytes/1024/1024 "size",(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "used",

substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "usage"

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

and b.tablespace_name='SYSTEM'

group by b.tablespace_name,b.file_name,b.bytes

order by b.tablespace_name;

//查询sys.AUD$表前100行

select * from sys.Aud$ where rownum<100;收起