Section 11: Recognizing and Managing Dependencies
11.01. Introduction to dependencies
♦False
2. View dept_view is based on a select from table departments. Procedure show_dept contains code which selects from dept_view. Which of the following statements are true? (Choose three.)
♦departments is indirectly dependent on show_dept
♦show_dept is directly dependent on dept_view (*)
♦dept_view is directly dependent on departments (*)
♦show_dept is indirectly dependent on departments (*)
♦emp_view is directly dependent on show_dept
3. A procedure includes the following code:
SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = 100;
Which of the following changes to the employees table will allow the procedure to be recompiled successfully ? (Choose two.)
♦The table is dropped but a public table exists with the same name and structure. (*)
♦The table is dropped.
♦A new column is added to the table. (*)
♦The table name is changed to newemps.
♦The first_name column is dropped from the table.
4. Which data dictionary view shows information about references and dependencies?
♦DEPTREE
♦USER_DEPENDENCIES (*)
♦USER_REFERENCES
♦USER_LOCAL_DEPENDENCIES
5. A single PL/SQL subprogram such as a procedure can be both a referenced object and a dependent object. True or False?
♦True (*)
♦False
6. Which of the following database objects are created when the utldtree.sql script is run? (Choose three.)
♦The utldtree table
♦The deptree_temptab table (*)
♦The deptree and ideptree views (*)
♦The deptree table
♦The deptree_fill procedure (*)
7. PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references table T. If table T is dropped, which of the following statements is true?
♦C is invalid but A and B are still valid
♦A, B and C are all invalid (*)
♦B and C are invalid but A is still valid
♦A, B and C are all still valid
♦None of the above
8. User ALICE owns a procedure show_emps which references table employees. Which of the following will generate information that shows this dependency?
♦BEGIN deptree_fill(‘TABLE’,’EMPLOYEES’);
END;
♦BEGIN deptree_fill(‘PROCEDURE’,’ALICE’,’SHOW_EMPS’);
END;
♦BEGIN deptree_fill(‘ALICE’,’TABLE’,’EMPLOYEES’);
END;
♦BEGIN deptree_fill(‘TABLE’,’ALICE’,’EMPLOYEES’);
END;
(*)
♦BEGIN deptree_fill(‘ALICE’,’PROCEDURE’,’SHOW_EMPS’);
END;
9. A procedure show_emps contains the following declaration:
CURSOR emp_curs IS SELECT last_name, salary FROM employees;
What will happen to the procedure if a new column is added to the employees table?
♦The procedure will still be valid and execute correctly because it does not reference the added column.
♦The procedure will automatically be dropped and must be recreated.
♦The procedure will be marked invalid and must be recompiled before it can be reexecuted. (*)
♦Users’ privileges to execute the procedure will automatically be revoked.
10. A SELECT from DEPTREE produced the following output.
>NESTED_LEVEL >TYPE >NAME
>0 >TABLE >EMPLOYEES
>1 >VIEW >EMP_VW
>2 >PROCEDURE >ADD_EMP
>1 >PROCEDURE >QUERY_EMP
♦ADD_EMP is directly dependent on EMPLOYEES
♦ADD_EMP is directly depedent on EMP_VW (*)
♦QUERY_EMP is directly dependent on ADD_EMP
♦EMP_VW is directly dependent on EMPLOYEES (*)
11. Procedure get_depts has been marked invalid because one of the objects it references has been altered. Which of the following statements are true? (Choose two.)
♦The procedure will be recompiled automatically the next time it is invoked. The recompilation will always be successful.
♦The procedure will be recompiled automatically the next time it is invoked. The recompilation may or may not be successful. (*)
♦The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts COMPILE; (*)
♦The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts RECOMPILE;
♦The procedure does not need to be recompiled.
12. Which of the following statements will show whether procedure myproc is valid or invalid?
♦SELECT status FROM USER_OBJECTS
WHERE object_type = ‘PROCEDURE’
AND object_name = ‘MYPROC’;
(*)
♦SELECT status FROM USER_PROCEDURES
WHERE procedure_name = ‘MYPROC’;
♦SELECT valid FROM USER_OBJECTS
WHERE object_type = ‘PROCEDURE’
AND object_name = ‘MYPROC’;
♦SELECT * FROM deptree;
13. Which of the following will NOT help to minimize dependency failures? (Choose two.)
♦SELECTing a list of column names instead of using SELECT * (*)
♦Declaring records using the %ROWTYPE attribute
♦Including a column list with INSERT statements
♦Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL constraint (*)
♦Declaring scalar variables using the %TYPE attribute
14. Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) is recreated.
emp_proc will be marked invalid and needs to be recompiled. True or False?
♦True
♦False (*)
11.02. Understanding the Remote Dependencies
1. A remote dependency is when a dependent object resides on a database on a separate node. True or False?
♦True (*)
♦False
2. Which statement for setting a database parameter is the default for remote dependency checking?
♦ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*)
♦ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE
♦ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP
♦ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE
3. With remote dependencies, one master data dictionary that resides on one server identifies the status of all schema objects. True or False?
♦True
♦False (*)
4. In this scenario, the following status is given for each procedure: – Procedure A is local, executed, and invalidated because the remote Procedure B time stamp does not match the local time stamp for Procedure B – Procedure A is recompiled. In Timestamp Mode, now Procedure A will execute successfully. True or False?
♦True (*)
♦False
5. In this scenario, the following status is given for each procedure:
– Procedure A is local and has a time stamp of 10 AM
– Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM
In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False?
♦True
♦False (*)
6. In Signature Mode, a compiled procedure is still valid if its dependent procedure has a parameter data type change from NUMBER to INTEGER.
♦True (*)
♦False
7. In Signature Mode, a procedure will not compile if the signatures of the remote dependencies do not match. True or False?
♦True (*)
♦False
♦False (*)
InfoCad Blog 