2

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

2

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;
    /
    
    • Please find the above result for the query provided. As you said AUD$ is consuming maximum space. – akr Apr 3 '17 at 8:41
    • Do I have any other option to handle this? Can I truncate lob segments shown in this schema? – akr Apr 3 '17 at 9:33
    • No, you can't because these objects are belong to SYS schema which owns data dictionary and meta data and we don't know the purpose of these objects. – JSapkota Apr 3 '17 at 10:06
    • Database dump export activity is scheduled to happen on daily basis. If SYSTEM tablespace is getting filled up solely because of this, is there any way to overcome this? Can we delete its related components after our use? – akr Apr 3 '17 at 10:20

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.