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
- Common causes of a large
SYSTEMtablespace are discussed in Why is my SYSTEM tablespace so big?. - Relocating tablespace datafiles is covered in this article.