Space Management

These questions are based on 1Z0-045 – Oracle Database 10g DBA New Features for Oracle8i OCPs.


Objective: Space Management
Sub-Objective: Reclaim wasted space from tables and indexes using the segment shrink functionality


Multiple Answer, Multiple Choice


You need to shrink the EMP table to release the unused space below the High Water Mark (HWM). You executed the ALTER TABLE EMP ENABLE ROW MOVEMENT statement before shrinking the EMP table. Which two statements are true about executing this statement? (Choose two.)



  1. This statement would return an error on execution.
  2. This statement enables row movement activity within the EMP table.
  3. To shrink the EMP table, the execution of this statement is a prerequisite.
  4. The ENABLE ROW MOVEMENT clause can be specified only during creation of the EMP table.

Answer:
B. This statement enables row movement activity within the EMP table.
C. To shrink the EMP table, the execution of this statement is a prerequisite.


Tutorial:
When you execute the ALTER TABLE EMP ENABLE ROW MOVEMENT statement, row movement activity within the EMP table is enabled. This statement is a prerequisite before you shrink the table with the ALTER TABLE EMP SHRINK SPACE statement. If you do not execute the ALTER TABLE EMP ENABLE ROW MOVEMENT statement before shrinking the EMP table, an error would be generated. Shrinking of the table involves movement of rows within the segment, causing the row identifiers (ROWIDs) of the table to change. Therefore, the ALTER TABLE EMP ENABLE ROW MOVEMENT statement should be executed before shrinking to enable movement of rows within the segment.


This command would not give an error on execution. The ALTER TABLE EMP ENABLE ROW MOVEMENT command would execute successfully and would enable row movement activity within the EMP table.


The ENABLE ROW MOVEMENT clause can be specified during creation of the EMP table and after creation of the table by using the ALTER TABLE command.


Reference:
1. Oracle University – Oracle Database 10g: New Features for Administrators – Space Management
D17079GC10
Edition 1.0
February 2004
D39100


2. Oracle University – Oracle Database 10g: New Features for Administrators Space Management
D17079GC21
Edition 2.1
October 2005
D22243


3. Oracle Database Administrator’s Guide
10g Release 2 (10.2)
Part Number B14231-02
Chapter: 14 Managing Space for Schema Objects
Topic: Shrinking Database Segments Online
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#CBBBIADA

Like what you see? Share it.Google+LinkedInFacebookRedditTwitterEmail
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: