oracle11g aud$ Maintenance

The system table space usage is 85%, and the aud$ table used to record audit records takes up a lot of space.

After the backup truncate off aud$, the problem is temporarily resolved. Remember oracle11.2 can move aud$ to normal table empty

Room So I tried it and it was OK.

1. Check system table space usage

2. Sql*plus:release 11.2.0.3.0 Production on Mon Mar 11:21:18
4. Copyright (c) 1982, Oracle.  All rights reserved.
6. Connected to :
8. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
10. With the partitioning, Data Mining and Real application testing Options
16. sql> Select
18. 2 b.tablespace_name "table space",
20. 3 b.bytes/1024/1024 "size M",
22. 4 (b.bytes-sum (NVL (a.bytes,0)))/1024/1024 "used M",
24. 5 substr ((b.bytes-sum (NVL (a.bytes,0))/(b.bytes) *100,1,5) "Utilization"
26. 6 from dba_free_space a,dba_data_files b
28. 7 where a.file_id=b.file_id
30. 8 and b.tablespace_name=' SYSTEM '
32. 9 GROUP by B.tablespace_name,b.file_name,b.bytes
34. Ten order by B.tablespace_name;
38. Table space size m using m utilization
40. ------------------- ---------- ----------- ----------
42. SYSTEM 4096 3485.9375 85.10

2. From Dba_segments, find the large object occupying the top 10 in the system table space.

[SQL]View Plaincopy

2. Sql> Col segment_name for A15;
4. Sql> SELECT *
6. 2 from (SELECT segment_name, SUM (BYTES)/1024/1024 MB
8. 3 from dba_segments
10. 4 WHERE tablespace_name = ' SYSTEM '
12. 5 GROUP by segment_name
14. 6 ORDER by 2 DESC)
16. 7 WHERE ROWNUM < 10;
20. Find out the aud$ occupy a lot of space.

3. Prepare Truncate aud$ table

[SQL]View Plaincopy

2. Sql> Show Parameter Audit_trail
6. NAME TYPE VALUE
8. --------------- ----------- ---------
10. Audit_trail string DB
14. <span style="Color:rgb (255, 0, 0); >--truncate aud$ table requires relevant permissions. </span>
16. sql> truncate table aud$;
18. Table truncated

4.truncate Check System tablespace usage and find usage reduced from 85% to 29.44%

[SQL]View Plaincopy

2. sql> ANALYZE TABLE aud$ COMPUTE STATISTICS;
4. Table analyzed.
8. sql> Select
10. 2 b.tablespace_name "table space",
12. 3 b.bytes/1024/1024 "size M",
14. 4 (b.bytes-sum (NVL (a.bytes,0)))/1024/1024 "used M",
16. 5 substr ((b.bytes-sum (NVL (a.bytes,0))/(b.bytes) *100,1,5) "Utilization"
18. 6 from dba_free_space a,dba_data_files b
20. 7 where a.file_id=b.file_id
22. 8 and b.tablespace_name=' SYSTEM '
24. 9 GROUP by B.tablespace_name,b.file_name,b.bytes
26. Ten order by B.tablespace_name;
30. Table space size m using m utilization
32. ---------------- ---------- ----------   ----------
34. SYSTEM 4096 1206 29.44

5. To avoid the effect of full system table space on the data, migrate the aud$ from the system table space to the normal tablespace aud_file.

[SQL]View Plaincopy

2. sql> BEGIN
4. 2 dbms_audit_mgmt. Set_audit_trail_location (
6. 3 Audit_trail_type = Dbms_audit_mgmt. AUDIT_TRAIL_AUD_STD,
8. 4 Audit_trail_location_value = ' aud_file ');
10. 5 END;
12. 6/
16. Sql> Col owner for A5;
18. Sql> SELECT OWNER, table_name, Tablespace_name
20. 2 from dba_tables
22. 3 WHERE table_name = ' aud$ '
24. 4 and OWNER = ' SYS ';
28. OWNER table_name Tablespace_name
30. ----- ------------------------------ ------------------------------
32. SYS aud$ TD_FILB

oracle11g aud$ Maintenance