If your database is very large or contains an unusually large number of objects, columns in tables, constraint definitions, users, or other definitions, the tables that make up the data dictionary might at some point be unable to acquire additional extents. For example, a data dictionary table may need an additional extent, but there is not enough contiguous space in the SYSTEM tablespace. If this happens, you cannot create new objects, even though the tablespace intended to hold the objects seems to have sufficient space. To remedy this situation, you can change the storage parameters of the underlying data dictionary tables to allow them to be allocated more extents, in the same way that you can change the storage settings for user-created segments. For example, you can adjust the values of NEXT or PCTINCREASE for the data dictionary table.
SEG$ | segments defined in the database (including temporary segments) |
OBJ$ | user-defined objects in the database (including clustered tables); indexed by I_OBJ1 and I_OBJ2 |
UNDO$ | rollback segments defined in the database; indexed by I_UNDO1 |
FET$ | available free extents not allocated to any segment |
UET$ | extents allocated to segments |
TS$ | tablespaces defined in the database |
FILE$ | files that make up the database; indexed by I_FILE1 |
FILEXT$ | datafiles with the AUTOEXTEND option set on |
TAB$ | tables defined in the database (includes clustered tables); indexed by I_TAB1 |
CLU$ | clusters defined in the database |
IND$ | indexes defined in the database; indexed by I_IND1 |
ICOL$ | columns that have indexes defined on them (includes individual entries for each column in a composite index); indexed by I_ICOL1 |
COL$ | columns defined in tables in the database; indexed by I_COL1 and I_COL2 |
CON$ | constraints defined in the database (includes information on constraint owner); indexed by I_CON1 and I_CON2 |
CDEF$ | definitions of constraints in CON$; indexed by I_CDEF1, I_CDEF2, and I_CDEF3 |
CCOL$ | columns that have constraints defined on them (includes individual entries for each column in a composite key); indexed by I_CCOL1 |
USER$ | users and roles defined in the database; indexed by I_USER1 |
TSQ$ | tablespace quotas for users (contains one entry for each tablespace quota defined for each user) |
C_OBJ# | cluster containing TAB$, CLU$, ICOL$, IND$, and COL$: indexed by I_OBJ# |
C_TS# | cluster containing FET$, TS$, and FILE$; indexed by I_TS# |
C_FILE#_BLOCK# | cluster containing SEG$ and UET$; indexed by I_FILE#_BLOCK# |
C_USER# | cluster containing USER and TSQ$$; indexed by I_USER# |
C_COBJ# | cluster containing CDEF$ and CCOL$; indexed by I_COBJ# |
C_TS# | if the free space in your database is very fragmented |
C_OBJ# | if you have many indexes or many columns in your tables |
CON$, C_COBJ# | if you use integrity constraints heavily |
C_USER# | if you have a lot of users defined in your database |
If you receive this error message and the segment you were trying to change (such as a table or rollback segment) has not reached the limits specified for it in its definition, check the storage settings for the object that contains its definition.
For example, if you received an ORA-1547 while trying to define a new PRIMARY KEY constraint on a table and there is sufficient space for the index that Oracle must create for the key, check if CON$ or C_COBJ# cannot be allocated another extent; to do this, query DBA_SEGMENTS and consider changing the storage parameters for CON$ or C_COBJ#.
See Also: For more information, see "Displaying Segments that Cannot Allocate Additional Extents" .