My system tablespace has 8mb as free space.
I have read somewhere :
*The dba has to do root analysis as to why the system tablespace has no enough space
before adding a datafile or resizing the existing datafile
source:
http://dba.stackexchange.com/questions/30631/expanding-system-tablespace
I have used this query to find out whether there are objects that consumes large portion
of the space:
sql> SELECT owner,segment_name,segment_type
,bytes/(1024*1024) size_m
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY size_m DESC
Here is the output:
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_M
======= =============== ============= ======
SYS IDL_UB1$ TABLE 230
SYS SOURCE$ TABLE 135
SYS AUD$ TABLE 96
SYS IDL_UB2$ TABLE 29
SYS C_TOID_VERSION# CLUSTER 21
SYS C_OBJ#_INTCOL# CLUSTER 19
SYS I_SOURCE1 INDEX 12
SYS JAVA$MC$ TABLE 11
SYS C_OBJ# CLUSTER 11
MOAMALAT DOCINFO TABLE 11
SYS ARGUMENT$ TABLE 11
SYS IDL_CHAR$ TABLE 10
How to further analyze to proceed with the proper solution (either to resize or to do other workaround)?
Note that my tablespace system is autoextensible:
SQL> SELECT tablespace_name,autoextensible FROM DBA_DATA_FILES WHERE tablespace_name='SYSTEM';
OUTPUT:
TABLESPACE_NAME AUTOEXTENSIBLE
================ ===============
SYSTEM YES
I am using oracle 11g