Miscellaneous New Features

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

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

ABOUT THE AUTHOR

Posted in Archive|

Comment: