Space Management   «Prev  Next»

Lesson 8Extents in the data dictionary
ObjectiveStorage parameters from Oracle data dictionary.

Oracle Data Dictionary Storage Parameters

Question: What role do 'extents' have in the Oracle data dictionary?
In Oracle databases, 'extents' play a crucial role in the allocation and management of storage space for database objects such as tables and indexes. An extent is a contiguous set of data blocks allocated to a database object within a tablespace. The Oracle data dictionary maintains information about extents to track and manage space utilization efficiently.
The role of extents in the Oracle data dictionary can be elaborated as follows:
  1. Storage Allocation: Extents enable Oracle to allocate storage space to database objects in a granular manner. When an object, such as a table or an index, requires additional space, Oracle allocates a new extent to accommodate the growth. This allows for efficient space management and dynamic growth of objects as needed.
  2. Space Management: The Oracle data dictionary maintains metadata about the extents, such as their size, location, and the objects they are associated with. This information is stored in data dictionary views like DBA_EXTENTS, USER_EXTENTS, and ALL_EXTENTS. By querying these views, database administrators can monitor and manage space utilization, identify fragmentation, and optimize storage allocation for improved performance.
  3. Extent Management: Oracle tablespaces can have two types of extent management: dictionary-managed and locally managed. In dictionary-managed tablespaces, the data dictionary keeps track of extents and their allocation using tables such as SYS.UET$ and SYS.FET$. In locally managed tablespaces, Oracle uses bitmaps to manage extent allocation, reducing contention and improving performance. Nevertheless, the data dictionary still maintains information about extents for locally managed tablespaces in the aforementioned views.
  4. Storage Reclamation: When a database object is dropped or truncated, the extents associated with the object are released, and the space is returned to the tablespace. The data dictionary plays a vital role in tracking and managing the released extents, ensuring that the freed space can be reused for other objects.

Extents are a fundamental aspect of Oracle's storage architecture, and the data dictionary plays a crucial role in managing and maintaining information about extents. This information aids database administrators in monitoring and optimizing space utilization, ensuring efficient storage allocation and database performance.
As with all the other attributes of database objects discussed in this course, you can explore the settings for storage parameters through views in the data dictionary.

Database Objects

The USER_TABLES, USER_INDEXES, and USER_TABLESPACES views have columns that store the values for the storage parameters you can assign to these objects.
INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, and PCT_INCREASE.
These columns hold the current values for these parameters.

Other views

There are other data dictionary views that provide information about how your Oracle database is using its storage space:

USER_EXTENTS: This view gives information about specific extents within a tablespace, including the relative position of an extent within the tablespace. A tablespace can contain many different database objects, and each object has its own extents, so the extents for any database object within a tablespace are usually mixed in with the extents for other database objects.
USER_SEGMENTS: The USER_SEGMENTS view has information about the segments belonging to a user, including the standard settings for INITIAL_EXTENT, MAX_EXTENTS, MIN_EXTENTS, and PCT_INCREASE. This view also contains a column called NEXT, but this column contains the value of the next extent that will be allocated, not the original value of the NEXT parameter.

Using Storage Manager

You can use the Storage Manager component of the Oracle Enterprise Manager, as shown in the following simulation, to check the available space in a tablespace as well as some of the dependencies on the tablespace.

Oracle Tablespace and Storage Manager

  1. You have already selected the Storage Manager menu choice from the DBA Management Pack group of the Oracle Enterprise Manager program group. Click Storage Manager.
  2. You should log in as an existing user--in this case, use the user name of sys and a password of sys to see a variety of tablespaces. Once you have entered the username and password, click OK to bring up Storage Manager.
  3. When Oracle Storage Manager comes up, click Tablespaces in the list box at left. (Note: The display has been resized from the default display for better understanding in this simulation.)
  4. The size of each tablespace is displayed in the right hand frame, along with the amount of space it used. If you want to explore the tablespaces further, you can select a particular tablespace. In this case, click the USER_DATA tablespace.
  5. Once you have selected a particular tablespace, you can click the Show Dependencies toolbar button at far left, the third button from the top.
  6. The Dependencies dialog comes up, by default, with the Dependencies tab showing. You can click the plus sign to the left of the Datafile folder to display the datafiles associated with the tablespace.
  7. You can get more information about the tablespace by clicking on the Dependents tab of the Dependencies window.
  8. This tab of the Dependencies window shows the logical entities that reside in the USER_DATA tablespace. You can show the tables that reside in this tablespace by expanding the Tables portion of the USER_DATA tree.
  9. From here, you can drill down into further dependencies by expanding a particular table, which shows you the dependencies of other database objects on the table. This is the end of the simulation.

Extents and Segments

From a physical point of view, a datafile is stored as operating system blocks. From a logical point of view, datafiles have three intermediate organizational levels: 1) data blocks, 2) extents, and 3) segments. An extent is a set of data blocks that are contiguous within an Oracle datafile. A segment is an object that takes up space in an Oracle database, such as a table or an index that is composed of one or more extents.
When Oracle updates data, it first attempts to update the data in the same data block. If there is not enough room in the data block for the new information, Oracle will write the data to a new data block that could be in a different extent. This discussion is especially important if you are running an older release of Oracle. Oracle Database 10g added a Segment Advisor that greatly simplifies reclaiming unused space in current database versions.

The next lesson demonstrates how Oracle uses free space within data blocks.