1Z0-082 - Oracle Database Administration I Managing Storage Structures Questions and Answers — Questions and Answers
Question 1: 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?
- Smallfile tablespace
- Bigfile tablespace (Correct answer)
- Undo tablespace
- Temporary tablespace
Correct 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.
Question 2: Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from smallest to largest unit?
- Extent, Data Block, Segment, Tablespace
- Data Block, Segment, Extent, Tablespace
- Data Block, Extent, Segment, Tablespace (Correct answer)
- Segment, Extent, Data Block, Tablespace
Correct 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.
Question 3: 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?
- SEGMENT SPACE MANAGEMENT MANUAL
- EXTENT MANAGEMENT DICTIONARY
- SEGMENT SPACE MANAGEMENT AUTO (Correct answer)
- EXTENT MANAGEMENT AUTO
Correct 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.
Question 4: 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?
- ALTER TABLE ... DEALLOCATE UNUSED
- Online Segment Shrink (Correct answer)
- Resumable Space Allocation
- Data Pump Export/Import
Correct 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.
Question 5: An extent is a logical unit of database storage allocation. Which of the following statements is true regarding extents?
- An extent can contain data from multiple data files.
- An extent is made up of a number of non-contiguous data blocks.
- An extent is allocated to a segment, and all extents for a segment must be in the same tablespace. (Correct answer)
- A segment can only have one extent.
Correct 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.
Question 6: What is the primary content of the Row Directory section within an Oracle data block header?
- The actual row data, including all column values.
- A bitmap indicating the free space within the block.
- Information about the tables that have rows stored in the block.
- Address information for each row piece stored in that block. (Correct answer)
Correct 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.
1Z0-082 - Oracle Database Administration I Managing Storage Structures Questions and Answers
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?