1z1-062 Real Exam Prep DEMO

Question 1: You are the DBA supporting an Oracle 11g Release 2 database and wish to move a table
containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table's indexes, to
another tablespace.
The table does not have a primary key and is used by an OLTP application.
Which technique will move the table and indexes while maintaining the highest level of availability to
the application?
A. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the indexes.
B. Oracle Data Pump.
C. Edition-Based Table Redefinition.
D. Online Table Redefinition.
E. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to move the indexes.
Correct Answer: D
Explanation
* Oracle Database provides a mechanism to make table structure modifications without significantly
affecting the availability of the table. The mechanism is called online table redefinition. Redefining
tables online provides a substantial increase in availability compared to traditional methods of
redefining tables.
* To redefine a table online:
Choose the redefinition method: by key or by rowid
* By key-Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys
are unique keys with all component columns having NOT NULL constraints. For this method, the
versions of the tables before and after redefinition should have the same primary key columns. This is
the preferred and default method of redefinition.
* By rowid-Use this method if no key is available. In this method, a hidden column named M_ROW $ $
is added to the post-redefined version of the table. It is recommended that this column be dropped
or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the
final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE
... DROP UNUSED COLUMNS statement to drop it.
You cannot use this method on index-organized tables.
Note:
* When you rebuild an index, you use an existing index as the data source. Creating an index in this
manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an
index based on an existing data source removes intra-block fragmentation. Compared to dropping
the index and using the CREATE INDEX statement, re-creating an existing index offers better
performance.
Incorrect:
Not E: Edition-based redefinition enables you to upgrade the database component of an application
while it is in use, thereby minimizing or eliminating down time.