LLI Wiki — My Digital Knowledge Base

Oracle: abnormally large SYSTEM tablespace

Queries to find the large segments of the SYSTEM tablespace and reclaim unused datafile space.

When the SYSTEM tablespace grows unexpectedly, first find out which segments take the space, then reclaim the unused space in the datafiles. This note collects the diagnostic queries and a resize script.

List the datafiles of a tablespace

col ts_name form a14  word wrap
col file_name form a34 word wrap
set pause on pages 24 pause 'wahey> '
spool dbf_lst.lst
select
      file_name                          file_name
      ,to_char(file_id,'9999')           f_id
      ,tablespace_name                   ts_name
      ,to_char(bytes/(1024*1024),'999,999') size_M
      ,to_char(blocks/1000,'99,999')     thou_blks
from
      sys.dba_data_files
where
      tablespace_name like upper('&ts_name'||'%')
order by tablespace_name,file_name
/
spool off
clear colu

Find the large segments in SYSTEM

select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_m
 from dba_segments
 where tablespace_name = 'SYSTEM'
 and    bytes/(1024*1024) > 1
 order by size_m desc;

Reclaim unused space in the datafiles

The query generates one alter database datafile ... resize command per datafile, down to the high-water mark. &&blksize is a SQL*Plus substitution variable: it prompts for the database block size (in bytes) on first use. Review the generated commands before running them.

set pages 0
set lines 300
column cmd format a300 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
       ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
          group by file_id ) b
where a.file_id = b.file_id(+)
    and ceil( blocks*&&blksize/1024/1024) -
         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

Increase the max extents of a dictionary index

An Oracle Support case related to an abnormally large SYSTEM tablespace used this procedure on the index I_H_OBJ#_COL#. It must run in upgrade mode:

SQL> shutdown immediate
SQL> startup upgrade
SQL> alter index I_H_OBJ#_COL# storage (maxextents &nnnnn);
Enter value for nnnnn: 10000
Index altered.
SQL> shutdown immediate
SQL> startup

Rebuild the data dictionary objects after a manual database creation

When a database is created manually, these scripts must be run afterwards:

Script Purpose
catalog.sql creates the data dictionary views
catproc.sql creates the system stored procedures
pupbld.sql creates the default roles and profiles
sqlplus /nolog
connect / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/pupbld.sql

Notes

Mastodon LLIMastodon SocialFosstodon