1Z0-146 | All About Virtual 1Z0-146 examcollection
Q21. The database instance was recently started up. Examine the following parameter settings for the database instance: NAME TYPE VALUE
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
You reset the value for the result_cache_max_size parameter by issuing the following command:
SQL> ALTER SYSTEM SET result_cache_max_size = 1056k SCOPE = BOTH;
System altered.
Which statement is true in this scenario?
A. 1056 KB is allocated for the result cache and the result cache is enabled.
B. 1056 KB is allocated for the result cache, but the result cache is disabled.
C. The results for only the queries that have the RESULT_CACHE hint are cached.
D. The results for all the queries except those having the NO_RESULT_CACHE hint are cached.
Answer: B
Q22. Examine the structure of the TEST_DETAILS table: Name Null? Type
TEST_ID NUMBER DESCRIPTION CLOB DESCRIPTION data was entered earlier and saved for TEST_ID 12.
You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION column for TEST_ID 12:
DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := '0123456789'
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
/
It generates an error on execution.
What correction should you do to achieve the required result?
A. WRITEAPPEND must be replaced with APPEND.
B. The BUF variable data type must be changed to CLOB. C. FOR UPDATE must be added to the SELECT statement.
D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.
Answer: C
Q23. Which two types of query results cannot be stored in the query result cache? (Choose two.)
A. subquery results
B. results of a query having the SYSDATE function
C. results of a query having the GROUP BY clause
D. results of a query having the DATE data type in the WHERE clause
Answer: A,B
Q24. Examine the code in the following PL/SQL block:
DECLARE
TYPE NumList IS TABLE OF INTEGER;
List1 NumList := NumList(11,22,33,44);
BEGIN
List1.DELETE(2);
DBMS_OUTPUT.PUT_LINE
( 'The last element# in List1 is ' || List1.LAST ||
' and total of elements is '||List1.COUNT);
List1.EXTEND(4,3);
END;
/
Which two statements are true about the above code? (Choose two.)
A. LAST and COUNT give different values.
B. LAST and COUNT give the same values.
C. The four new elements that are added contain the value 33.
D. The four new elements that are added contain the value 44.
Answer: A,C
Q25. Which two statements are true about the SQL Query Result Cache? (Choose two.)
A. It can store the query results for temporary tables.
B. It can be set at the system, session, or query level.
C. It is used only across statements in the same session.
D. Cached query results become invalid when the data accessed by the query is modified.
Answer: B,D
Q26. Examine the following structure: SQL> DESCRIBE user_identifiers Name Null? Type
NAME VARCHAR2(30)
SIGNATURE VARCHAR2(32)
TYPE VARCHAR2(18)
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_TYPE VARCHAR2(13)
USAGE VARCHAR2(11)
USAGE_ID NUMBER
LINE NUMBER
COL NUMBER
USAGE_CONTEXT_ID NUMBER
Identify two scenarios in which information is stored in the USAGE column. (Choose two.)
A. an assignment made to VARIABLE
B. declaration of a variable or formal parameter
C. an identifier passed to a subprogram in IN OUT mode
D. execution of the GOTO statement or raise of an exception
Answer: A,B
Q27. Which two statements are true about the query results stored in the query result cache? (Choose two.)
A. If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
B. A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.
C. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
D. A query result for a query that has a bind variable is stored in the cache and is reused if the query is equivalent even when the bind variable has a different value.
Answer: A,B
Q28. You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?
A. The corresponding shared library is also removed from the system.
B. PL/SQL subprograms can be used to invoke the external C procedure.
C. The existing extproc process is terminated and a new extproc is started.
D. The PL/SQL subprogram that depends on the external C program becomes invalid.
Answer: D
Q29. Examine the following parameter values for a session:
PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 2
Which two statements are true in this scenario? (Choose two.)
A. The compiler automatically inlines subprograms even if you specify that they not be inlined.
B. The compiler optimizes PL/SQL programs by elimination of unnecessary computations and exceptions.
C. PL/SQL statements in a PL/SQL program unit are compiled into machine-readable code, and stored in the SYSTEM tablespace.
D. PL/SQL statements in a PL/SQL program unit are compiled into an intermediate form, machine-readable code, which is stored in the database dictionary.
Answer: B,C
Q30. View the Exhibit and examine the PL/SQL code.
The code takes a long time to execute. What would you recommend to improve performance?
A. using NOT NULL constraint when declaring the variables
B. using the BULK COLLECT option for query instead of cursor
C. using WHILE.. END LOOP instead of FOR .. END LOOP
D. using the SIMPLE_INTEGER data type instead of the NUMBER data type
Answer: B