Fundamentals I Ch7 - Segments and Storage Structures

Chapter 7 - Segments and Storage Structures
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla

Review Questions


1. Place the following logical storage structures in order—from the smallest logical storage unit to the largest.


A. Segment
B. Block
C. Tablespace
D. Extent

---
Ans:

B, D, A, and C. A data block is the smallest logical storage unit in Oracle. An extent is a group of contiguous blocks. A segment consists of one or more extents. A segment can belong to only one tablespace. A tablespace can have many segments.


2. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?


A. Temporary segment
B. Redo log buffer
C. Undo buffer
D. Rollback segment
---
Ans: D.
Before any DML operation, the undo information (before-image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data.


3. Which parameter specifies the number of transaction slots in a data block?


A. MAXTRANS
B. INITRANS
C. PCTFREE
D. PCTUSED
----
Ans:
B. INITRANS specifies the number of transaction slots in a data block. Oracle uses a transaction slot when the data block is being modified. INITRANS reserves space for the transactions in the block.
MAXTRANS specifies the maximum number of concurrent transactions allowed in the block. The default of INITRANS for a block in a data segment is 1, and the default for the block in an index segment is 2.


4. Select the statement that is not true regarding undo tablespaces.


A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE statement.
B. Two undo tablespaces may be active if a new undo tablespace was specified and there are pending transactions on the old one.
C. You can switch from one undo tablespace to another.
B. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.

----
Ans:
A. If a specific undo tablespace is not defined in the CREATE DATABASE statement, Oracle automatically creates one with the name SYS_UNDOTBS .


5. Which of the following database objects consists of more than one segment?


A. Nested Table
B. Partitioned table
C. Index Partition
D. Undo segment
E. None of the above
---
Ans: B.
A partitioned table consists of multiple table partition segments in different tablespaces.

Notes: How about Nested Table?
Nested Table - If a table has columns that are tables themselves (nested tables), each column is stored in its own segment. Each segment may have its own storage parameters.


6. Which of the following segment allocation parameters is ignored when automatic segment space management is in effect for a tablespace?


A. FREELISTS
B. PCTFREE
C. INITRANS
D. MAXTRANS
----
Ans: A. Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.

Notes: PCTUSED is ignored too.


7. Which data dictionary view would you query to see the free extents in a tablespace?


A. DBA_TABLESPACES
B. DBA_FREE_SPACE
C. DBA_EXTENTS
D. DBA_SEGMENTS
---
Ans:
B. DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.


8. Which two data dictionary views can account for the total amount of space in a data file?


A. DBA_FREE_SEGMENTS
B. DBA_FREE_SPACE
C. DBA_SEGMENTS
D. DBA_EXTENTS
---
Ans: B, D. The sum of the free space in DBA_FREE_SPACE plus the space allocated for extents in DBA_EXTENTS should add up to the total space specified for that tablespace. DBA_FREE_SEGMENTS is not a valid data dictionary view, and DBA_SEGMENTS only contains the number of extents and blocks allocated to each segment.


9. Which portion of the data block stores information about the table having rows in this block?


A. Common and variable header
B. Row directory
C. Table directory
D. Row data

Ans:
C. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.


10. When does Oracle stop adding rows to a block?


A. When free space reaches the PCTFREE threshold
B. When row data reaches the PCTFREE threshold
C. When free space drops below the PCTUSED threshold
D. When row data drops below the PCTUSED threshold
---
Ans: A.

The PCTFREE and PCTUSED parameters are used to manage the free space in the block. Oracle inserts rows into a block until the free space falls below the PCTFREE threshold. PCTFREE is the amount of space reserved for future updates. Oracle considers adding more rows to the block only when the free space falls below the PCTUSED threshold.


11. What main restriction is placed on tablespaces defined with automatic segment space management?


A. The tablespace cannot contain nested tables.
B. The tablespace cannot be transportable.
C. The tablespace cannot contain LOBs.
D. The bootstrap segment cannot reside in a tablespace that has automatic segment space management enabled.
----
Ans:
C. Table segments that have LOBs cannot reside in a locally managed tablespace that has automatic segment space management enabled.


12. Which dynamic performance view can help you adjust the size of an undo tablespace?

A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
----
Ans:
A. The V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE parameters, can be used to calculate an optimal undo tablespace size when database activity is at its peak.


13. What is the default value of PCTFREE?

A. 40
B. 0
C. 100
D. 10
---
Ans:
D. The default value of PCTFREE is 10, and the default for PCTUSED is 40.


14. Which data dictionary view can you query to see the OPTIMAL value for a rollback segment?


A. DBA_ROLLBACK_SEGS
B. V$ROLLSTAT
C. DBA_SEGMENTS
D. V$ROLLNAME
----
Ans:

B. You can query the OPTIMAL value from the V$ROLLSTAT view. This view does not show the offline rollback segments.


15. What is row migration?


A. A single row spread across multiple blocks
B. Moving a table from one tablespace to another
C. Storing a row in a different block when there is not enough room in the current block for the row to expand
D. Deleting a row and adding it back to the same table
---
Ans:
C. Row migration is the movement of a row from one block to a new block. Row migration occurs when a row is updated and its new size cannot fit into the free space of the block; Oracle moves the row to a new block, leaving a pointer in the old block to the new block. You can avoid this problem by either setting a higher PCTFREE value or specifying a larger block size at database creation.


16. What can cause the Snapshot too old error?


A. Smaller rollback extents
B. Higher MAXEXTENTS value
C. Larger rollback extents
D. Higher OPTIMAL value
----
Ans:
A. Smaller rollback extents can cause the Snapshot too old error if there are long-running queries in the database.


17. The sum of the values PCTFREE and PCTUSED cannot exceed which of the following:


A. 255
B. DB_BLOCK_SIZE
C. The maximum is operating system dependent.
D. 100
---

D. These two numbers are percentages that are defined as the percentage of a given block, and since these areas cannot overlap, the sum cannot be greater than 100 percent.


18. Which of the following statements may require a temporary segment?


A. CREATE TABLE
B. CREATE INDEX
C. UPDATE
D. CREATE TABLESPACE
-----
Ans:
B. Operations that require a sort may need a temporary segment (when the sort operation cannot be completed in the memory area specified by SORT_AREA_SIZE ). Queries that use DISTINCT , GROUP BY , ORDER BY , UNION , INTERSECT , or MINUS clauses also need a sort of the result set.


19. How does Oracle determine the extent sizes for a temporary segment?


A. From the initialization parameters
B. From the tables involved in the sort operation
C. Using the default storage parameters for the tablespace
D. The database block size
---
Ans:

C. The default storage parameters for the tablespace determine the extent sizes for temporary segments.


20. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per __________.


A. Table
B. Segment
C. Extent
D. Block

---
Ans:

D. MAXTRANS specifies the maximum allowed concurrent transactions per block. Oracle needs transaction space for each concurrent transaction in the block s variable header. You can pre allocate space by specifying INITRANS .