Oracle&11g&移出aud$&到users表空间
11g 移出aud$ 到users表空间把三张表，2个索引，2个blob列移出system表空间 SQL select
table_name,tablespace_name from dba_tables where table_name
in('AUDIT$','AUD$','AUDIT_ACTIONS'); TABLE_NAME TABLESPACE_NAME
------------------------------ -
11g 移出aud$ 到users表空间
把三张表，2个索引，2个blob列移出system表空间
SQL& select table_name,tablespace_name from
dba_tables where table_name
in('AUDIT$','AUD$','AUDIT_ACTIONS');
TABLE_NAME&&&&&&&&&&&&&&&&&&&&
TABLESPACE_NAME
------------------------------ ------------------------------
AUDIT$&&&&&&&&&&&&&&&&&&&&&&&&
AUD$&&&&&&&&&&&&&&&&&&&&&&&&&&
AUDIT_ACTIONS&&&&&&&&&&&&&&&&&
SQL& select index_name,tablespace_name from
dba_indexes where table_name
in('AUDIT$','AUD$','AUDIT_ACTIONS');
INDEX_NAME&&&&&&&&&&&&&&&&&&&&
TABLESPACE_NAME
------------------------------ ------------------------------
SYS_ILC00040$$&&&&&&
SYS_ILC00041$$&&&&&&
I_AUDIT&&&&&&&&&&&&&&&&&&&&&&&
I_AUDIT_ACTIONS&&&&&&&&&&&&&&&
SQL& select
table_name,column_name,segment_name,tablespace_name from user_lobs
where table_name='AUD$';
TABLE_NAME&&&&&&&&&&&&&&&&&&&&
COLUMN_NAME&&&&&&&&&
SEGMENT_NAME&&&&&&&&&&&&&&&&&&
TABLESPACE_NAME
------------------------------ --------------------
------------------------------ ------------------------------
AUD$&&&&&&&&&&&&&&&&&&&&&&&&&&
SQLBIND&&&&&&&&&&&&&
SYS_LOBC00040$$&&&&&
AUD$&&&&&&&&&&&&&&&&&&&&&&&&&&
SQLTEXT&&&&&&&&&&&&&
SYS_LOBC00041$$&&&&&
SQL& alter table AUDIT$ m
Table altered
SQL& alter table AUD$ m
Table altered
SQL& alter table AUDIT_ACTIONS move tablespace
Table altered
SQL& alter table AUD$ move lob(SQLBIND) store as
SYS_LOBC00040$$ (tablespace users);
Table altered
SQL& alter table AUD$ move lob(SQLTEXT) store as
SYS_LOBC00041$$ (tablespace users);
Table altered
SQL& alter index I_AUDIT rebuild online tablespace
Index altered
SQL& alter index I_AUDIT_ACTIONS rebuild online
Index altered
alter table AUDIT$
alter table
AUDIT_ACTIONS move tablespace
alter table AUD$
alter table AUD$
move lob(SQLBIND) store as
SYS_ILC00041$$ (tablespace
alter table AUD$
move lob(SQLTEXT) store as
SYS_ILC00041$$ (tablespace users);
alter index I_AUDIT rebuild
alter index I_AUDIT_ACTIONS
rebuild onl
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。博客访问： 93967
博文数量： 35
注册时间：
分类： Oracle 11:06:27
system表空间不足，增长很快。是由于开启了审计功能。 & & & & & & & 注：转载请注明出处及连接地址~~~
于是进行详细的查询：
查看system表空间里磁盘占用较大的segment
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;
select A.SEGMENT_NAME,SUM(A.BYTES)/24 GB,A.SEGMENT_TYPE &&
from dba_extents a,v$datafile b,v$tablespace C&
where b.TS#=C.TS# AND C.NAME='SYSTEM' AND A.FILE_ID=B.FILE#
GROUP BY A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY GB DESC;
select DECODE( PARTITION_NAME, NULL, segment_name, segment_name || ':' || PARTITION_NAME) segment_name ,&
& & & &segment_type, nvl(initial_extent, 0) initial_extent, nvl(next_extent, 0) next_extent,
& & & &nvl(extents, 0) extents, nvl(bytes, 0) bytes, Owner, nvl(max_extents, 0)max_extents&
from & dba_segments
where &tablespace_name = 'SYSTEM'
可看到sysytem表空间中，AUD$表达到了161个G，占system的98%以上。
审计结果存储在system表空间中的SYS.AUD$表中，通过视图dba_audit_trail查看
NAME & & & & & & & & TYPE & &VALUE & & & & & & & & & & & & &
-------------------- ------- ------------------------------&
audit_file_dest & & &string &/usr/oracle/admin/mpj/adump&
audit_sys_operations boolean FALSE & & & & & & & & & & & & &
audit_syslog_level & string & & & & & & & & & & & & & & & &&
audit_trail & & & & &string &DB &
audit_trail & & & 说明数据库开启的是DB级别的审计。
发现数据库启用了DB级别的审计功能，相关审计级别如下：
None：是默认值，不做审计；
DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；
DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；
OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；
XML：10g里新增的。
select * from dba_stmt_audit_
发现都为by_access。
& & & 查看触发oracle审计的策略：
& & &1)By session / By Access
& & & & &by session对每个session中发生的重复操作只记录一次
& & & & &by access对每个session中发生的每次操作都记录，而不管是否重复。
& & 2)Whenever successful/ Whenever not successful
& & & & Whenever successful表示操作成功以后才记录下来。
& & & & Whenever not successful表示操作失败后才记录下来。
关闭审计命令：alter system set audit_trail=none scope= &该参数为静态参数，需要重启数据库
由于移动不让关闭审计
解决方法：
导出AUD$表：
exp &system/oracle & dumpfile=.dmp directory=arch_tables &logfile=mpj_.log&TABLES=AUD$
truncate表：truncate table AUD$；
resize system 表空间：
col file_id for 999
col file_name for a50;
col size_m for 9999;
col free_m for 9999;
col use_m for 9999;
select a.*,size_m-free_m use_m,'alter database datafile '||file_id||' resize '||ceil(size_m-free_m)||'m;' resize_file
from (select a.file_id, a.file_name,a.bytes/ size_m, b.bytes/ free_m from dba_data_files a, dba_free_space b
& & & where a.file_id=b.file_id and b.tablespace_name=upper(trim('&ts_name')) group by a.file_id,a.file_name,a.bytes, b.bytes) a
order by file_id,ceil(size_m-free_m)
select a.file#, &
& & & & &a.name, &
& & & & &a.bytes / 1024 / 1024 CurrentMB, &
& & & & &ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo, &
& & & & &(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB, &
& & & & &'alter database datafile ''' || a.name || ''' resize ' || &
& & & & &ceil(HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd &
& from v$datafile a, &
& & & & &(SELECT file_id, MAX(block_id + blocks - 1) HWM &
& & & & & & FROM DBA_EXTENTS &
& & & & & &GROUP BY file_id) b &
& where a.file# = b.file_id(+) &
& And (a.bytes - HWM * a.block_size) >0 &
& order by 5
法三：同学给的
select a.file#, &
& & & & &a.name,&
& & & & &c.tablespace_name,&
& & & & &round(a.bytes / 1024 / 1024) CurrentMB, &
& & & & &ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo, &
& & & & &(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB, &
& & & & &'alter database datafile ' || a.FILE# || ' resize ' || &
& & & & &round(ceil(HWM * a.block_size) / 1024 / 1024 +5) || 'M;' ResizeCmd &
& from v$datafile a, &
& & & & &(SELECT file_id, MAX(block_id + blocks - 1) HWM &
& & & & & & FROM DBA_EXTENTS &
& & & & & &GROUP BY file_id) b,
& & & & & &dba_data_files c &
& where a.file# = b.file_id(+) &
&And (a.bytes - HWM * a.block_size) >0 &
&and a.FILE#=c.file_id
&and c.tablespace_name not in ('SYSTEM','SYSAUX')
&and c.tablespace_name not like '%UNDO%'
& order by 6 desc；
将审计结果表从system表空间里移动到别的表空间上
1.手动迁移需停库
alter table sys.aud$ m
alter table sys.aud$ move lob(sqlbind) store as( tablespace USERS);
alter table sys.aud$ move lob(SQLTEXT) store as( tablespace USERS);
alter index sys.I_AUD1 rebuild tablespace users；
alter table AUDIT$ m
alter table AUDIT_ACTIONS m&
alter table AUD$ m
alter table AUD$ move lob(SQLBIND) store as SYS_ILC00041$$ (tablespace users);&
alter table AUD$ move lob(SQLTEXT) store as SYS_ILC00041$$ (tablespace users);
alter index I_AUDIT rebuild onl
alter index I_AUDIT_ACTIONS rebuild onl
--可能修改值(index和lob index)
SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';
COLUMN_NAME & & & & & & & & & & & & & & &INDEX_NAME
---------------------------------------- ------------------------------
SQLBIND & & & & & & & & & & & & & & & & &SYS_ILC00040$$
SQLTEXT & & & & & & & & & & & & & & & & &SYS_ILC00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';
INDEX_NAME
------------------------------
SYS_ILC00040$$
SYS_ILC00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';
INDEX_NAME
------------------------------
SQL> &SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';
INDEX_NAME
------------------------------
I_AUDIT_ACTIONS
2.自动迁移不需要停库 &
迁移前：查看所属表空间
SQL> select segment_name,tablespace_name from dba_segments where
& 2 &segment_name in('AUD$','SYS_ILC00040$$','SYS_ILC00041$$',
& 3 &'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
AUDIT$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &SYSTEM
AUD$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &SYSTEM
SYS_ILC00041$$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & &SYSTEM
SYS_ILC00040$$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & &SYSTEM
I_AUDIT_ACTIONS & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
I_AUDIT & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
DBMS_AUDIT_MGMT实现迁移
conn / as sysdba&
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
验证DBMS_AUDIT_MGMT效果
SQL> select segment_name,tablespace_name from dba_segments where
& 2 & segment_name in('AUD$','SYS_ILC00040$$','SYS_ILC00041$$',
& 3 & 'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
AUDIT$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &SYSTEM
AUD$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &USERS
SYS_ILC00041$$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & &USERS
SYS_ILC00040$$ & & & & & & & & & & & & & & & & & & & & & & & & & & & & &USERS
I_AUDIT_ACTIONS & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
I_AUDIT & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & SYSTEM
迁移完整解决方法：
1.创建表空间
create tablespace mpj&
& & & &datafile '/oracle/OMT/admin/oradata/mpj01.dbf' &size 2G
SELECT table_name, tablespace_name
&FROM dba_tables
&WHERE table_name IN ('AUD$', 'FGA_LOG$')
&ORDER BY table_
2.aud$表移动到新tablespace
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'MPJ');
3.sys用户procedure：
create or replace procedure&
sp_trunc_audit_log is
& &execute immediate
& & & &'truncate table aud$';
grant execute on sp_trunc_audit_
4.system用户procedure：
create or replace procedure&
sp_job_trunc_audit_log is
& & sys.sp_trunc_audit_
5.自动调度job
& DBMS_SCHEDULER.CREATE_JOB&
& & job_name & => 'day_trunc_audit_log',
& & job_type & => 'STORED_PROCEDURE',
& & job_action => 'SP_JOB_TRUNC_AUDIT_LOG',
& & start_date => sysdate,
& & repeat_interval => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=05;INTERVAL=1',
& & enabled & & & & => true,
& & comments & & & &=> 'every day truncate table audit log'
SYS@MPJ>col segment_name format a30
SYS@MPJ>select segment_name,tablespace_name from dba_segments where segment_name in('AUD$','SYS_ILC00040$$','SYS_ILC00041$$','AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME & & & & & & & & & TABLESPACE_NAME
------------------------------ ------------------------------
I_AUDIT_ACTIONS & & & & & & & &SYSTEM
I_AUDIT & & & & & & & & & & & &SYSTEM
AUDIT_ACTIONS & & & & & & & & &SYSTEM
AUDIT$ & & & & & & & & & & & & SYSTEM
AUD$ & & & & & & & & & & & & & MPJ
SYS_ILC00041$$ & & & MPJ
SYS_ILC00040$$ & & & MPJ
阅读(3775) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。下次自动登录
现在的位置:
& 综合 & 正文
Oracle审计表AUD$数据过大问题
1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.
2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.SQL&CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;Now export the table as,SQL& host exp tables=AUDIT_RECORD file=audit_record.dmp
3)To delete all records from audit trail table SYS.AUD$ issue,SQL&DELETE FROM SYS.AUD$;
To delete all records of particular audited table from the audit trail issue,SQL&DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';
But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.
4)Truncate audit table to reduce size.SQL&CONN / as sysdbaSQL&TRUNCATE TABLE SYS.AUD$
【上篇】【下篇】查看: 1285|回复: 18
insert into sys.aud$1小时10多万条，这是什么原因啊
认证徽章论坛徽章:39
从AWR看到，不到10小时，以下两条语句执行超过100万次，这是什么情况，标准审计审计create session吗？可select count(*) from v$session会话总数不到200条。
insert into sys.aud$( sessionid, entryid, statement, ntimestamp#, userid, userhost, terminal, action#, returncode, logoff$lread, logoff$pread, logoff$lwrite, logoff$dead, logoff$time, comment$text, spare1, clientid, sessioncpu, proxy$sid, user$guid, instance#, process#, auditid, dbid) values(:1, :2, :3, SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, :12, cast(SYS_EXTRACT_UTC(systimestamp) as date), :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)
nsert into sys.aud$( sessionid, entryid, statement, ntimestamp#, userid, userhost, terminal, action#, returncode, obj$creator, obj$name, auth$privileges, auth$grantee, new$owner, new$name, ses$actions, ses$tid, logoff$pread, logoff$lwrite, logoff$dead, comment$text, spare1, spare2, priv$used, clientid, sessioncpu, proxy$sid, user$guid, instance#, process#, xid, scn, auditid, sqlbind, sqltext, obj$edition, dbid) values(:1, :2, :3, SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36)
认证徽章论坛徽章:39
select SESSIONID,NTIMESTAMP#,LOGOFF$TIME from sys.aud$ order by 1 desc结果大致如下
(37.67 KB, 下载次数: 2)
10:24 上传
认证徽章论坛徽章:39
感觉像是快速的登入登出
论坛徽章:306
对比下，都插入什么信息？哪些信息是变化的，
论坛徽章:186
你不要做登录审计就ok了,仅仅审计不成功的登录.
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
认证徽章论坛徽章:24
应该是大量的连接风暴吧
认证徽章论坛徽章:39
对比下，都插入什么信息？哪些信息是变化的，
多谢版主回复，比对有些困难，sys.aud$记录已经达亿条了
认证徽章论坛徽章:39
你不要做登录审计就ok了,仅仅审计不成功的登录.
NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;
感谢回复，试一试看看
论坛徽章:306
你看这个审计表记录的信息： select action_name,count(*) from dba_audit_trail group by action_
认证徽章论坛徽章:39
你看这个审计表记录的信息： select action_name,count(*) from dba_audit_trail group by action_
主要是登入登出
LOGON& & & &
LOGOFF& & & &
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有
北京市公安局海淀分局网监中心备案编号：10 广播电视节目制作经营许可证：编号（京）字第1149号Oracle审计表AUD$处理方法
审计表AUD$处理方法
Oracle版本：11.2.0，其他版本要测试DBMS_AUDIT_MGMT能否成功
1. 查询表，然后truncate
select count(*) from aud$;
truncate table aud$;
select count(*) from aud$;
2.创建表空间
create tablespace adttbs
datafile '/oracle/OMT/admin/oradata/aud01.dbf'
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_
3.aud$表移动到新tablespace
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type =& DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value =& 'ADTTBS');
4.sys用户procedure：
create or replace procedure
sp_trunc_audit_log is
execute immediate
'truncate table aud$';
grant execute on sp_trunc_audit_
5.system用户procedure：
create or replace procedure
sp_job_trunc_audit_log is
sys.sp_trunc_audit_
6.自动调度job
DBMS_SCHEDULER.CREATE_JOB
=& 'day_trunc_audit_log',
=& 'STORED_PROCEDURE',
job_action =& 'SP_JOB_TRUNC_AUDIT_LOG',
start_date =& sysdate,
repeat_interval =& 'FREQ=DAILY; BYHOUR=04; BYMINUTE=05;INTERVAL=1',
=& 'every day truncate table audit log'
