oracle rac 下oracle审计写到SYStem表空间了,system 空间满了,怎么移除来?
如果是11G以后可以用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
1、建立新表空间
2、移走
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'NEW TABLESPACE');
END;
/
更老的只能手工来,建立新表和新表空间,新表按照原表建索引,两个表互换名字。清空原表。
收起前一段时间遇见了同样的问题。环境是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;