SYSTEM tablespace getting filled up

oracleoracle-11g-r2tablespaces

My SYSTEM tablespace is getting filled up enormously within a few days. I've added another dbf file when it was filled up completely earlier. Now, the second .dbf is also about to fill. The only process that's happening on daily basis is an export database for logical backup. Also, tablespace auto extent is ON. What might be the reason for this tablespace fill up? How can I tackle this situation?
I'm using Oracle 11g r2 in archivelog mode.

Attaching result for below query:

SELECT owner, segment_name, segment_type, extents, blocks, bytes 
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;

enter image description here

Best Answer

First of all, make sure that SYSTEM tablespace is not assigned as default or temporary tablespace for normal database users.

Second of all, one of the space consuming table in SYSTEM tablespace is AUD$. Next, as you have said you are just performing export/import, datapump creates master tables in the default tablespace of the user performing export job.

The master table is either retained or dropped, depending on the circumstances, as given here. You might have done the export using SYS user. If the job is failed master table will not be dropped.

Now, you can use the following query to see which object is consuming most of the space on this tablespace.

SELECT owner, segment_name, segment_type, extents, blocks, bytes 
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;

Update:

If you need the audit trail then copy its records to another table, another tablespace if needed, and/or export the audit table, otherwise you can truncate it.

Login as sqlplus sys as sysdba

SQL> CREATE TABLE backup_aud$ AS SELECT * from sys.aud$;
SQL> truncate table aud$;

OR

You can moveaudit trail table, SYS.AUD$, to another tablespace using DBMS_AUDIT_MGMT

  • Create new tablespace
  • Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

    SQL> 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;
/
    • Related Question