Managing Storage Structures Flashcards

A database administrator is creating a new tablespace for a large-scale data warehousing application that will store terabytes of data. To simplify file management and support extremely large data volumes within a single file, which type of tablespace should be created?

Answer: Bigfile tablespace

A Bigfile tablespace is designed to contain a single, very large data file (or temp file), which can be up to 128 terabytes for a 32K block size. This simplifies the management of data files for very large databases (VLDBs) by reducing the number of files a DBA has to manage. Smallfile tablespaces are the traditional type and can contain many data files, but each file has a smaller size limit. Undo and temporary tablespaces serve specific purposes (transaction rollback and sorting operations, respectively) and do not inherently address the need for managing massive, single-file data storage.

Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from smallest to largest unit?

Answer: Data Block, Extent, Segment, Tablespace

The logical storage hierarchy in an Oracle database is as follows: The smallest unit is the Data Block. A set of contiguous data blocks forms an Extent. A set of extents allocated for a specific object (like a table or index) is called a Segment. Finally, a Tablespace is a logical container for segments.

A DBA is creating a new locally managed tablespace and wants to simplify space management within segments by letting Oracle manage free and used space automatically using bitmaps. Which clause should be included in the `CREATE TABLESPACE` statement?

Answer: SEGMENT SPACE MANAGEMENT AUTO

Automatic Segment Space Management (ASSM) is enabled for a locally managed tablespace by specifying `SEGMENT SPACE MANAGEMENT AUTO`. This method uses bitmaps to track the status of blocks within a segment, which is more efficient and simplifies administration compared to the manual method that uses freelists. `EXTENT MANAGEMENT DICTIONARY` is an older method for managing extents at the data dictionary level, and `MANUAL` specifies the use of freelists, not automatic bitmap-based management.

You are tasked with reclaiming fragmented free space within a table's segment to improve the performance of full table scans. The space is currently below the high water mark (HWM). Which Oracle feature must be used to accomplish this online?

Answer: Online Segment Shrink

Online Segment Shrink is the feature designed to reclaim fragmented space both above and below the high water mark (HWM) by compacting data and moving the HWM. This is an online operation. `ALTER TABLE ... DEALLOCATE UNUSED` only reclaims space above the HWM. Resumable Space Allocation is for suspending and resuming large operations, and while Data Pump can be used to rebuild objects, it is a much more involved process than the dedicated shrink operation.

An extent is a logical unit of database storage allocation. Which of the following statements is true regarding extents?

Answer: An extent is allocated to a segment, and all extents for a segment must be in the same tablespace.

A segment is a collection of extents, and all of these extents must reside within the same tablespace. However, a segment can span multiple data files within that tablespace. An extent itself is a set of *contiguous* data blocks and cannot span data files; all blocks for a given extent must come from a single data file. Segments typically have many extents, allocated as the object grows.

What is the primary content of the Row Directory section within an Oracle data block header?

Answer: Address information for each row piece stored in that block.

The Row Directory, located in the data block header, contains entries for each row piece in the block. These entries store the address of the corresponding row piece in the row data area of the block. The actual row data is stored in the 'Row Data' section. The 'Table Directory' contains information about the tables owning the rows, and free space is managed separately.