OCP 1Z0 053 19

19.A PL/SQL procedure queries only those columns of a redefined table that were unchanged by the 
online table redefinition. What happens to the PL/SQL procedure after the online table redefinition? 
A. It remains valid. 
B. It becomes invalid for all options of online table redefinition but automatically gets revalidated the next 
time it is used. 
C. It becomes invalid for all options of online table redefinition and is automatically recompiled during 
online redefinition of the table. 
D. It becomes invalid only if the storage parameters have been modified and it automatically gets 
revalidated the next time it is used. 
Answer: A 

http://docs.oracle.com/cd/B28359_01/server.111/b28279/chapter1.htm#FEATURENO07633

1.2.9.5 Minimize Dependent PL/SQL Recompilation After Online Table Redefinition

This feature minimizes the need to recompile dependent PL/SQL packages after an online table redefinition. If the redefinition does not logically affect the PL/SQL packages, recompilation is not needed. This optimization is on by default.

This feature reduces the time and effort to manually recompile dependent PL/SQL after an online table redefinition. This also includes views, synonyms, and other table dependent objects (with the exception of triggers) that are not logically affected by the redefinition.

11G新特性，但实验不理想。谁有通过验正的步骤？

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as test@ORCLSQL> SQL> CREATE OR REPLACE PROCEDURE p_test AS  2  BEGIN  3    FOR cur IN (SELECT ename FROM emp WHERE rownum <= 2) LOOP  4      dbms_output.put_line(cur.ename);  5    END LOOP;  6  END;  7  /Procedure createdSQL> SET serveroutput ON;SQL> EXEC p_test;SMITHALLENPL/SQL procedure successfully completedSQL> SELECT status FROM user_objects WHERE object_name = upper('p_test');STATUS-------VALID1 row selectedSQL> DROP TABLE emp2 PURGE;Table droppedSQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;Table createdSQL> ALTER TABLE emp2 MODIFY ename VARCHAR2(22);Table alteredSQL> BEGIN  2  dbms_redefinition.can_redef_table(user,'emp',DBMS_REDEFINITION.CONS_USE_ROWID);  3  dbms_redefinition.start_redef_table(user, 'emp', 'emp2','',DBMS_REDEFINITION.CONS_USE_ROWID);  4  dbms_redefinition.finish_redef_table(USER, 'emp', 'emp2');  5  END;  6  /PL/SQL procedure successfully completedSQL> SELECT status FROM user_objects WHERE object_name = upper('p_test');STATUS-------INVALID1 row selectedSQL> EXEC p_test;SMITHALLENPL/SQL procedure successfully completedSQL> SELECT status FROM user_objects WHERE object_name = upper('p_test');STATUS-------VALID1 row selectedSQL>