Keep It Simple. Developer's tips.
Essential tips for freeing up storage in Oracle Database XE

Photo by Nikola Knezevic on Unsplash

Essential tips for freeing up storage in Oracle Database XE

Rafal Grzegorczyk's photo
Rafal Grzegorczyk
·

3 min read

Table of contents

I will share some practical tips and scripts that helped me to reclaim valuable space in my Oracle Database XE environment (12 GB limited space)

It worked on one of my environments that can not be upgraded to a higher Oracle DB (yet).

Oracle XE storage limitations

Tablespaces that are taken to count if your storage limits are exceeded:

  • USERS

  • SYSAUX

  • all that you will create

UNDOTBS1, TEMP and SYSTEM are NOT taken to count limit - source

Valuable scripts to identify what's going on

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES/1024 as mb
        from V$SYSAUX_OCCUPANTS
       order by 3 desc;

select owner, sum(bytes/1024/1024) as mb
  from DBA_SEGMENTS
 group by owner order by 2 desc;

select 'SQLDEV:LINK:'
       || User
       || ':TABLESPACE:'
       || a.TABLESPACE_NAME
       || ':oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as "Tablespace Name",
       Round(a.bytes_alloc / 1024 / 1024) "Allocated (MB)",
       Round(NVL(b.bytes_free, 0) / 1024 / 1024) "Free (MB)",
       Round((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024) "Used (MB)",
       Round((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Free",
       100 - Round((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Used",
       Round(a.maxbytes / 1024 / 1024) "Max. Bytes (MB)"
  from (select f.TABLESPACE_NAME, sum(f.BYTES) bytes_alloc, sum(Decode(f.AUTOEXTENSIBLE, 'YES', f.MAXBYTES, 'NO', f.BYTES))
  maxbytes from DBA_DATA_FILES f group by f.TABLESPACE_NAME) a
  left join (select f.TABLESPACE_NAME, sum(f.BYTES) bytes_free from DBA_FREE_SPACE f group by f.TABLESPACE_NAME) b
    on a.TABLESPACE_NAME = b.TABLESPACE_NAME union all select 'SQLDEV:LINK:'
       || User
       || ':TABLESPACE:'
       || h.TABLESPACE_NAME
       || ':oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as tablespace_name,
       Round(sum(h.BYTES_FREE + h.BYTES_USED) / 1048576) megs_alloc,
       Round(sum((h.BYTES_FREE + h.BYTES_USED) - NVL(p.BYTES_USED, 0)) / 1048576) megs_free,
       Round(sum(NVL(p.BYTES_USED, 0)) / 1048576) megs_used,
       Round((sum((h.BYTES_FREE + h.BYTES_USED) - NVL(p.BYTES_USED, 0)) / sum(h.BYTES_USED + h.BYTES_FREE)) * 100) Pct_Free,
       100 - Round((sum((h.BYTES_FREE + h.BYTES_USED) - NVL(p.BYTES_USED, 0)) / sum(h.BYTES_USED + h.BYTES_FREE)) * 100) pct_used,
       Round(sum(f.MAXBYTES) / 1048576) max
                                                         from (select distinct * from SYS.GV_$TEMP_SPACE_HEADER) h
                                                         left join (select distinct * from SYS.GV_$TEMP_EXTENT_POOL) p
                                                           on p.FILE_ID = h.FILE_ID
                                                          and p.TABLESPACE_NAME = h.TABLESPACE_NAME
                                                        inner join DBA_TEMP_FILES f
                                                           on f.FILE_ID = h.FILE_ID
                                                          and f.TABLESPACE_NAME = h.TABLESPACE_NAME
 group by h.TABLESPACE_NAME
 order by 2

Clean UNIFIED_AUDIT_TRAIL

select * from unified_audit_trail; --milions of rows

--clean it
begin
 dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,
    use_last_arch_timestamp=>FALSE);
end;

Truncate WRI$_SQLSET_PLAN_LINES

According to Oracle MySupport Doc ID2857648.1 it can just be truncated.

truncate table WRI$_SQLSET_PLAN_LINES;

--this size should be reduced after truncate
select OCCUPANT_NAME, SCHEMA_NAME, SPACE_USAGE_KBYTES / 1024 as mb
  from V$SYSAUX_OCCUPANTS
 where OCCUPANT_NAME = 'SM/OTHER'
 order by 3 desc;

--if your space is not reclaimed you would have to run this (from ROOT, not PDB)
 alter table WRI$_SQLSET_PLAN_LINES shrink space;

If shrink space doesn't work, then please proceed with this tips -> link

Check your AWR retention period

I don't have enough space (and it occupies SYSAUX), so I want to retain my AWR snapshots only for a short time.

--check yur retention period
SELECT SNAP_INTERVAL, RETENTION 
FROM DBA_HIST_WR_CONTROL;

--set retention for 8 days only / use minutes in params
begin
   dbms_workload_repository.modify_snapshot_settings(
      interval  => 30,
      retention => 11520
   );
end;

Read more about AWR retention periods here.

Truncate WRI$_ADV_OBJECTS

truncate table WRI$_ADV_OBJECTS;

Warning! If your WRI$_ADV_OBJECTS table is significant, then a vast UNDO operation will be required. To avoid that, use tips from this article

Reclaim space from LOB SEGMENTS

Identify large lob segments:

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

Choose wisely, and shrink some space e.g

delete from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$;
alter table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ enable row movement;
ALTER TABLE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ MODIFY LOB(BLOB_CONTENT) (SHRINK SPACE CASCADE);
alter table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ disable row movement;

Refer to this great Oracle-Base guide with more advanced tips on how to reclaim unused space.

If you have any other tips for freeing up some storage in XE, drop a comment, and I'll be happy to update this blog post.

OracleDatabasestipstips and tricks