Q1. Which three functions are performed by the SQL Tuning Advisor?

A. Building and implementing SQL profiles

B. Recommending the optimization of materialized views

C. Checking query objects for missing and stale statistics

D. Recommending bitmap, function-based, and B-tree indexes

E. Recommending the restructuring of SQL queries that are using bad plans

View Answer

Q2. You execute the following PL/SQL:

Which two statements are true?

A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE > 10000 is accessed. 

B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE > 10000 is accessed. 

C. FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.

D. FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements is captured in the FGA audit trial.

View Answer

Q3. The following parameter are set for your Oracle 12c database instance:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

You want to manage the SQL plan evolution task manually. Examine the following steps:

1. Set the evolve task parameters.

2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.

3. Implement the recommendations in the task by using the DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.

4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.

5. Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function. Identify the correct sequence of steps:

A. 2, 4, 5

B. 2, 1, 4, 3, 5

C. 1, 2, 3, 4, 5

D. 1, 2, 4, 5

View Answer

Q4. Examine the parameter for your database instance:

You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:

Identify the reason why the optimizer chose different execution plans.

A. The optimizer used a dynamic plan for the query.

B. The optimizer chose different plans because automatic dynamic sampling was enabled. 

C. The optimizer used re-optimization cardinality feedback for the query.

D. The optimizer chose different plan because extended statistics were created for the columns used. 

View Answer

Q5. You plan to migrate your database from a File system to Automata Storage Management (ASM) on same platform.

Which two methods or commands would you use to accomplish this task?

A. RMAN CONVERT command

B. Data Pump Export and import

C. Conventional Export and Import

D. The BACKUP AS COPY DATABASE . . . command of RMAN

E. DBMS_FILE_TRANSFER with transportable tablespace

View Answer

Q6. Your database is open and the LISTENER listener running. You stopped the wrong listener LISTENER by issuing the following command:

1snrctl > STOP

What happens to the sessions that are presently connected to the database Instance?

A. They are able to perform only queries.

B. They are not affected and continue to function normally.

C. They are terminated and the active transactions are rolled back.

D. They are not allowed to perform any operations until the listener LISTENER is started. 

View Answer

Q7. Your multitenant container (CDB) containing three pluggable databases (PDBs) is running in ARCHIVELOG mode. You find that the SYSAUX tablespace is corrupted in the root container.

The steps to recover the tablespace are as follows:

1.Mount the CDB. 

2.Close all the PDBs.

3.Open the database.

4.Apply the archive redo logs.

5.Restore the data file.

6.Take the SYSAUX tablespace offline.

7.Place the SYSAUX tablespace online.

8.Open all the PDBs with RESETLOGS.

9.Open the database with RESETLOGS.

10.Execute the command SHUTDOWN ABORT.

Which option identifies the correct sequence to recover the SYSAUX tablespace?

A. 6, 5, 4, 7

B. 10, 1, 2, 5, 8

C. 10, 1, 2, 5, 4, 9, 8

D. 10, 1, 5, 8, 10

View Answer

Q8. You are planning the creation of a new multitenant container database (CDB) and want to store the ROOT and SEED container data files in separate directories.

You plan to create the database using SQL statements. 

Which three techniques can you use to achieve this?

A. Use Oracle Managed Files (OMF).

B. Specify the SEED FILE_NAME_CONVERT clause.

C. Specify the PDB_FILE_NAME_CONVERT initialization parameter.

D. Specify the DB_FILE_NAMECONVERT initialization parameter.

E. Specify all files in the CREATE DATABASE statement without using Oracle managed Files (OMF).

View Answer

Q9. What are three purposes of the RMAN “FROM” clause?

A. to support PUSH-based active database duplication

B. to support synchronization of a standby database with the primary database in a Data environment

C. To support PULL-based active database duplication

D. To support file restores over the network in a Data Guard environment

E. To support file recovery over the network in a Data Guard environment

View Answer

Q10. Identify three scenarios in which you would recommend the use of SQL Performance Analyzer to analyze impact on the performance of SQL statements.

A. Change in the Oracle Database version

B. Change in your network infrastructure

C. Change in the hardware configuration of the database server

D. Migration of database storage from non-ASM to ASM storage

E. Database and operating system upgrade

View Answer