Q1. In your database, you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.

How would you accomplish this?

A. Setting a metric threshold

B. Implementing Database Resource Manager

C. Enabling resumable timeout for user sessions

D. Decreasing the value of the IDLE_TIME resource limit in the default profile

View Answer

Q2. You upgrade your Oracle database in a multiprocessor environment. As a recommended you execute the following script:

SQL > @utlrp.sql

Which two actions does the script perform?

A. Parallel compilation of only the stored PL/SQL code

B. Sequential recompilation of only the stored PL/SQL code

C. Parallel recompilation of any stored PL/SQL code

D. Sequential recompilation of any stored PL/SQL code

E. Parallel recompilation of Java code

F. Sequential recompilation of Java code

View Answer

Q3. Which three statements are true about Flashback Database?

A. Flashback logs are written sequentially, and are archived. 

B. Flashback Database uses a restored control file to recover a database.

C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area. 

D. Flashback Database can recover a database to the state that it was in before a reset logs operation.

E. Flashback Database can recover a data file that was dropped during the span of time of the flashback.

F. Flashback logs are used to restore to the blocks' before images, and then the redo data may be used to roll forward to the desired flashback time.

View Answer

Q4. You find this query being used in your Oracle 12c database:

Which method a used by the optimizer to limit the rows being returned?

A. A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the total rows

B. All the rows are returned to the client or middle tier but only the first 20 percent are returned to the screen or the application.

C. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.

D. A TOP-N query is created to limit the rows to 20 percent of the total rows

View Answer

Q5. A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions

The table has a local prefixed, range partitioned index.

Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions.

This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month.

You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned.

Which three methods could transparently help to achieve this result?

A. Using a partial local Index on the warehouse fact table month column with indexing disabled to the table partitions that return most of their rows to the queries.

B. Using a partial local Index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.

C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, which retains the existing local partitioned column.

D. Converting the partitioned table to a partitioned view that does a UNION ALL query on the monthly tables, which retains the existing local partitioned column.

E. Using a partial global index on the warehouse fact table month column with indexing disabling for the table partitions that return most of their rows to the queries.

F. Using a partial global index on the warehouse fact table month column with indexing disabled for the table partitions that return a few rows to the queries.

View Answer

Q6. Which two statements are true concerning the Resource Manager plans for individual pluggable databases (PDB plans) in a multitenant container database (CDB)?

A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB. 

B. In a PDB plan, subplans may be used with up to eight consumer groups.

C. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups across all PDBs in the CDB. 

D. If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated. 

E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.

View Answer

Q7. You enabled an audit policy by issuing the following statements:

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SCOTT;

SQL> AUDIT POLICY ORA_DATABASE_PARAMETER BY SYS, SYSTEM;

For which database users and for which executions is the audit policy now active? Select two.

A. SYS, SYSTEM

B. SCOTT

C. Only for successful executions

D. Only for failed executions

E. Both successful and failed executions

View Answer

Q8. Your database has the SRV1 service configured for an application that runs on middle-tier application server. The application has multiple modules. You enable tracing at the service level by executing the following command:

SQL > exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (‘SRV1’);

The possible outcome and actions to aggregate the trace files are as follows:

1.The command fails because a module name is not specified. 

2.A trace file is created for each session that is running the SRV1 service.

3.An aggregated trace file is created for all the sessions that are running the SRV1 service.

4.The trace files may be aggregated by using the trcess utility.

5.The trace files be aggregated by using the tkprof utility.

Identify the correct outcome and the step to aggregate by using tkprof utility?

A. 1

B. 2 and 4

C. 2 and 5

D. 3 and 4

E. 3 and 5

View Answer

Q9. You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12C.  Your database supports a mixed workload.  During the day, lots of insert, update, and delete operations are performed.  At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions.

After the upgrade, you notice that the performance of ETL jobs has degraded.  To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs.

How do you accomplish this?

A. Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.

B. Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file.

C. Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR view.

D. Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.

View Answer

Q10. Which three are true about the large pool for an Oracle database instance that supports

shared server connections?

A. Allocates memory for RMAN backup and restore operations

B. Allocates memory for shared and private SQL areas

C. Contains a cursor area for storing runtime information about cursors

D. Contains stack space

E. Contains a hash area performing hash joins of tables

View Answer