Miscellaneous New Features

  By —

These questions are based on 1Z0-050 – Oracle Database 11g New Features
Oracle Self Test Software Practice Test.

Objective: Miscellaneous new features.
Sub-objective: Describe and use the enhanced online table redefinition.

Single answer, multiple choice

You have a table named AR_TRX_HY that contains the following columns:

AR_TRX_ID
AR_TRX_DATE
AR_TRX_CUST_ID
AR_TRX_AMT

You have a PL/SQL procedure named GET_CUST_TRX that accepts a customer ID and a beginning and ending date, and returns customer accounts receivable transactions for a specific time period. Which statement about using online table redefinition on the AR_TRX_HY table is true?

1. Any online redefinition will invalidate the GET_CUST_TRX procedure.
2. If your redefinition affects only columns that are not referenced by the GET_CUST_TRX procedure, the procedure will not be invalidated.
3. If your redefinition adds a new column, the GET_CUST_TRX procedure will become invalid.
4. If your redefinition drops the AR_TRX_CUST_ID column, the GET_CUST_TRX procedure will remain valid.

Answer:
B. If your redefinition affects only columns that are not referenced by the GET_CUST_TRX procedure, the procedure will not be invalidated.

Tutorial:
If your redefinition affects only columns that are not referenced by the GET_CUST_TRX procedure, the procedure will not be invalidated. With Oracle 11g, only dependent objects that are logically affected by the redefinition and triggers are invalidated. The GET_CUST_TRX will remain valid and will not need to be recompiled the next time it is executed.

The option that states any online redefinition will invalidate the GET_CUST_TRX procedure is incorrect. Any online redefinition that does not logically affect the GET_CUST_TRX procedure can be performed, and the procedure is not invalidated.

The option that states if your redefinition adds a new column, the GET_CUST_TRX procedure will become invalid is incorrect. Adding a new column does not logically affect the GET_CUST_TRX procedure. Therefore, it remains valid.

The option that states if your redefinition drops the AR_TRX_CUST_ID column, the GET_CUST_TRX procedure will remain valid is incorrect. The GET_CUST_TRX procedure in this scenario queries the AR_TRX_HY by customer ID. Therefore, dropping the customer ID column would affect the GET_CUST_TRX procedure, and it would be invalidated.

Reference:
Oracle University - Oracle Database 11g: New Features For Administrators - Chapter 13: Miscellaneous New Features - Online Redefinition Enhancements
D50081GC10
Edition 1.0
July 2007
D51900

Oracle Database New Features Guide 11g Release 1 (11.1)
Part Number B28279-02
Oracle 11g Database New Features - Availability - Minimize Dependent PL/SQL Recompilation After Online Table Redefinition
http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07633