Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Changing Storage Parameters for the Data Dictionary

This section describes aspects of changing data dictionary storage parameters, and includes the following topics:

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.

Warning: Exercise caution when changing the storage settings for the data dictionary objects. If you choose inappropriate settings, you could damage the structure of the data dictionary and be forced to re-create your entire database. For example, if you set PCTINCREASE for the data dictionary table USER$ to 0 and NEXT to 2K, that table will quickly reach the maximum number of extents for a segment, and you will not be able to create any more users or roles without exporting, re-creating, and importing the entire database.

Structures in the Data Dictionary

The following tables and clusters contain the definitions of all the user-created objects in the database:

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#
Of all of the data dictionary segments, the following are the most likely to require changes:

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
For the clustered tables, you must change the storage settings for the cluster, not for the table.

Errors that Require Changing Data Dictionary Storage

Oracle returns an error if a user tries to create a new object that requires Oracle to allocate an additional extent to the data dictionary when it is unable to allocate an extent. The error message ORA-1547, "failed to allocate extent of size num in tablespace 'name'" indicates this kind of problem.

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" [*].


Contents Index Home Previous Next