Oracle database 11g:advanced pl/sql (1Z0-146) Free Practice Test

Question 1

Which two statements are true about the migration of BasicFile to the SecureFile format by using the DBMS_REDEFINITION package? (Choose two.)

A. It requires all constraints defined on the original table to be re-created manually after the migration.

B. It requires free space that is at least equal to the space used by the table that is migrated.

C. It can be performed only on tables with a single LOB column.

D. It allows the table that is migrated to be accessed throughout the migration process.

E. It automatically creates an interim table during the migration process.

Correct Answer: B,D

Question 2

Which two statements are true about SecureFile LOB options? (Choose two.)

A. The KEEP_DUPLICATES option removes the deduplication effect only on new data in a LOB column.

B. The DECRYPT option can be used to remove the encryption only if the LOB column is empty.

C. The KEEP_DUPLICATES option removes the deduplication effect on existing and new data in a LOB column.

D. The DECRYPT option can be used to remove the encryption from LOB columns that are empty or contain data.

Correct Answer: C,D

Question 3

Which two statements correctly describe the features of SecureFiles? (Choose two.)

A. Compression stores identical data occurring two or more times in the same LOB column as a single copy for the table.

B. Encryption stores the encryption keys for the LOB columns inside the database.

C. Compression does not entail table or index compression and vice-versa.

D. Encryption stores the encryption keys for the LOB columns outside the database.

Correct Answer: C,D

Question 4

You enabled PL/SQL tracing in a user session using the following command:
SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);
View Exhibit1 to examine the output. After some time, the query produces a different result as
shown in Exhibit2.

What is the cause for the change?

A. The PLSQL_DEBUG parameter is set to FALSE for the user session.

B. The FOO procedure has been compiled with the DEBUG option, and executed.

C. The FOO procedure has been executed more than once.

D. Schema level statistics have been gathered by the database administrator (DBA).

Correct Answer: B

Question 5

View the Exhibit and examine the output.

Which statement is an inference from the output?

A. The class file is pinned into the Java pool.

B. The class file has been loaded into the shared library.

C. Java class methods in the Java class file have been published.

D. The loadjava command has been executed to load the Java source and class files.

Correct Answer: D

Question 6

Examine the following error:
SQL> DECLARE
v_runid NUMBER;
BEGIN
v_runid := DBMS_HPROF.ANALYZE (LOCATION => 'PROFILE_DATA',
FILENAME => 'pd_cc_pkg.txt');
DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_runid);
END;
DECLARE
*ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_HPROF", line 299
ORA-06512: at line 4
What would you do to execute the above block successfully?

A. Start the PL/SQL profiler before executing the block.

B. Grant READ and WRITE privileges to the current user on the PROFILE_DATA directory object.

C. Run the dbmshptab.sql script located at ORACLE_HOME\RDBMS\ADMIN.

D. Run the tracetab.sql script located at ORACLE_HOME\RDBMS\ADMIN.

Correct Answer: C

Question 7

Which two statements are true about the query results stored in the query result cache? (Choose two.)

A. 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.

B. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.

C. 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.

D. 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.

Correct Answer: C,D

Question 8

You executed the following command to alter the session parameter:
SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
Which two statements are true in this scenario? (Choose two.)

A. All the identifiers declared in compiled program units before altering the parameter settings appear in the *_IDENTIFIER static data dictionary views.

B. All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data dictionary views.

C. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object.

D. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope collects data for the compiled object and stores it in the SYSTEM tablespace.

Correct Answer: B,C

Question 9

You created a procedure as follows:
CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)
IS
stmt VARCHAR2(100);
pname VARCHAR2(20);
BEGIN
stmt:='SELECT product_name FROM products WHERE product_id=:2';
EXECUTE IMMEDIATE stmt INTO pname USING twhr;
DBMS_OUTPUT.PUT_LINE(pname);
END;
/
View the Exhibit to examine the structure of PRODUCTS table.

Which statement is true about the procedure?

A. It can be invoked only from a PL/SQL block.

B. The values for bind arguments remain persistent in the session after the execution of the procedure.

C. It produces an error when invoked.

D. It reduces the chances of SQL injection by using bind arguments.

Correct Answer: D

Question 10

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 GROUP BY clause

C. results of a query having the DATE data type in the WHERE clause

D. results of a query having the SYSDATE function

Correct Answer: A,D

Welcome to TestSimulate

Oracle database 11g:advanced pl/sql (1Z0-146) Free Practice Test