oracle审计清理

mahan

审计迁移
1、本地创建备份表
create tablespace TSP_AUDBAK datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;

create table aud_bak tablespace TSP_AUDBAK as select * from aud$;

2、创建dblink
create public database link dblink_aud
connect to audqy identified by audqy
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 168.168.232.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)';

select * from dual@dblink_aud;
2、远端创建备份表
create tablespace TSP_AUDBAK datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;
alter tablespace TSP_AUDBAK add datafile '+DATA' size 32g;

create table sys.aud_bak tablespace tsp_audbak as select * from aud_bak@dblink_aud;



审计清理
1、查询
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name='AUD$';
select table_name, tablespace_name from dba_tables where table_name='AUD$' order by table_name;

2、截断AUD
truncate table aud$;

3、再次查询并确认
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name='AUD$';