Skip to Main Content
  • Questions
  • system tablespace is full with audit files

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, vishnudas.

Asked: August 30, 2018 - 6:22 am UTC

Last updated: August 31, 2018 - 1:45 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

hi there,

i have a problem here,my system tablespace is getting full
these are current values for my system tablespace


TABLESPACE_NAME FILE_NAME ALLOCATED_SIZE_IN_MB FREE_SIZE_IN_MB
------------------------------ --------------------------------------------------------------
SYSAUX D:\ORADATA\TESTDB\SYSAUX01.DBF 800 43.9375
SYSTEM D:\ORADATA\TESTDB\SYSTEM01.DBF 7510 63.5

my SYSTEM table has a size of 7.5 but only 63.5 mb remains

so i executed the following query

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,EXTENTS,BLOCKS,BYTES/1024/1024 as MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME='SYSTEM'
ORDER BY BYTES DESC;


OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS BLOCKS MB
------------------------------ --------------------------------------------------------------
SYS AUD$ TABLE 280 791168 6181
SYS IDL_UB1$ TABLE 116 46080 360
SYS SOURCE$ TABLE 105 34816 272


from the output audit files are about 6 gb in size i really don't know how to remove those files from aud$,
so please help me to solve this situation..


and Connor said...

Check out MOS note 1328239.1 which will walk you through the process, but paraphrasing

1) create a new tablespace

create tablespace audit_tbs datafile '/u01/app/oracle/oradata/d1v11202/audit_tbs1.dbf' size 100M autoextend on; 


2) move the tables there

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.



You can also query the following views to see what options are currently turned on (which will help explain why the tables are growing)

DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

vishnudas, August 31, 2018 - 7:30 am UTC

thank you sir,
it helped me a lot.. :)


More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.