Your 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.
Edition-Based Table Redefinition.
B.
Online Table Redefinition.
C.
An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the
indexes.
D.
Oracle Data Pump.
E.
An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to
move the indexes.
B
I’m really loving the theme/design of your weblog. Do you ever run into any web browser compatibility issues? A couple of my blog audience have complained about my site not operating correctly in Explorer but looks great in Firefox. Do you have any ideas to help fix this problem?
http://www.bastcilkdoptb.com/
Yep, definitely B
Similar as to 11g documentation:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm
“Online table redefinition enables you to:
Modify the storage parameters of a table or cluster
Move a table or cluster to a different tablespace
…”
The “The table does not have a primary key and is used by an
OLTP application.” is a red herring, as documented the process can use rowid if a primary key is not available:
“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.”