Introduction to Logical Storage Structures
Oracle Database allocates logical space for all data in the database. The logical units of database space allocation are data blocks, extents, segments, and table spaces. At the finest level of granularity, Oracle Database stores data in data blocks.
Locally Managed Table spaces
A locally managed table space maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks.
A locally managed table space has the following advantages:
■Avoids using the data dictionary to manage extents
■Tracks adjacent free space automatically
■Determines the size of locally managed extents automatically
Segment space management is an attribute inherited from the table space that contains the segment. Within a locally managed table space, the database can manage segments automatically or manually. For example, segments in table space users can be managed automatically while segments in table space tools are managed manually.
Automatic Segment Space Management The ASSM method uses bitmaps to manage space.
Bitmaps provide the following advantages:
■Dynamic affinity of space to instances in an Oracle Real Application Clusters (Oracle RAC) environment
Dictionary-Managed Table spaces
A dictionary-managed table space uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data.
Overview of Data Blocks
Oracle Database manages the logical storage space in the data files of a database in units called data blocks, also called Oracle blocks or pages. A data block is the minimum unit of database I/O.
Data Blocks and Operating System Blocks
At the physical level, database data is stored in disk files made up of operating system blocks. An operating system block is the minimum unit of data that the operating system can read or write. In contrast, an Oracle block is a logical storage structure whose size and structure are not known to the operating system.
Database Block Size
Every database has a database block size. The DB_BLOCK_SIZE initialization parameter sets the data block size for a database when it is created. The size is set for the SYSTEM and SYS AUX table spaces and is the default for all other table spaces. The database block size cannot be changed except by re-creating the database. If DB_BLOCK_SIZE is not set, then the default data block size is operating system-specific. The standard data block size for a database is 4 KB or 8 KB. If the size differs for data blocks and operating system blocks, then the data block size must be a multiple of the operating system block size.
Overview of Extents
An extent is a logical unit of database storage space allocation made up of contiguous data blocks. Data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.
Allocation of Extents
By default, the database allocates an initial extent for a data segment when the segment is created. An extent is always contained in one data file. Although no data has been added to the segment, the data blocks in the initial extent are reserved for this segment exclusively. The first data block of every segment contains a directory of the extents in the segment.
If the initial extent becomes full, and if more space is required, then the database automatically allocates an incremental extent for this segment. The allocation algorithm depends on whether the table space is locally managed or dictionary-managed. In the locally managed case, the database searches the bitmap of a data file for adjacent free blocks. If the data file has insufficient space, then the database looks in another data file. Extents for a segment are always in the same table space but may be in different data files.
De allocation of Extents
In general, the extents of a user segment do not return to the table space unless you drop the object using a DROP command. In Oracle Database 11g Release 2 (18.104.22.168),you can also drop the segment using the DBMS_SPACE_ADMIN package. For example,if you delete all rows in a table, then the database does not reclaim the data blocks for use by other objects in the table space.
In some circumstances, you can manually de allocate space. The Oracle Segment Advisor helps determine whether an object has space available for reclamation based on the level of fragmentation in the object. The following techniques can free extents:
■You can use an online segment shrink to reclaim fragmented space in a segment.
■You can move the data of a non partitioned table or table partition into a new segment, and optionally into a different table space for which you have quota.
■You can rebuild or coalesce the index.
■You can truncate a table or table cluster, which removes all rows. By default,Oracle Database de allocates all space used by the removed rows except that specified by the MIN EXTENTS storage parameter. In Oracle Database 11g Release 2(22.214.171.124), you can also use TRUNCATE with the DROP ALL STORAGE option to drop entire segments.
■You can de allocate unused space, which frees the unused space at the high watermark end of the database segment and makes the space available for other segments in the table space
When extents are freed, Oracle Database modifies the bitmap in the data file for locally managed table spaces to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.