1Z0-074 Online Practice Questions and Answers

You notice a large number of latch: cache buffers lru chain and latch: cache buffers chains wait events.

Which four might be reasons for these? (Choose four.)

A. The same small number of blocks are repeatedly accessed by different sessions simultaneously.

B. The buffer cache is undersized.

C. Cursors are closed explicitly after each execution.

D. A large number of connected user sessions are idle.

E. DBWR is not writing the dirty buffers fast enough.

F. Blocks are ageing out of the buffer cache frequently.

G. Too many sort operations are being performed.

QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED are set to TRUSTED and TRUE respectively in an Oracle 12c Release 2 database. You create the SALES_EVAL real-time materialized view (MV) on the SALES, CUSTOMERS, and PRODUCTS tables, which supports COMPLETE REFRESH by executing these statements.

There is a constraint on the PRODUCTS table with ENABLE, NOVALIDATE, and RELY attributes. No updates have been done to SALES, CUSTOMERS, or PRODUCTS since the MV was created. From where are rows returned and why?

A. from the SALES_EVAL MV because the QUERY_REWRITE_ENABLED setting permits rewrites on unverified constraints if the table is not stale

B. from the SALES, CUSTOMERS, and PRODUCTS tables because QUERY_REWRITE_INTEGRITY is set to TRUSTED

C. from the SALES, CUSTOMERS, and PRODUCTS tables because the column in the GROUP BY clause is different from the SALES_EVAL MV definition

D. from the SALES, CUSTOMERS, and PRODUCTS tables because the columns used in the SELECT list are different from the SALES_EVAL MV definition

E. from the SALES_EVAL MV because the WHERE clause in the query and in the SALES_EVAL definition are the same

Which two statements are true about the In-Memory (IM) column store? (Choose two.)

A. It may be used for columns in Index Organized Tables (IOTs)

B. It may be used for columns in clustered tables

C. IM area size is not controlled by Oracle Automatic Memory Management

D. IM area size can be set at the CDB and PDB levels

E. IM area size can be increased or decreased dynamically

F. It is not used for partitioned tables

You plan to use Information Lifecycle Management (ILM) for the ORDERS table.

Which is true about Heat Map statistics when HEAT_MAP is ON?

A. They are collected for all segments except for those in the SYSTEM and SYSAUX tablespaces, and are stored in the SYSTEM tablespace.

B. They are collected for all segments except for those in the SYSTEM and SYSAUX tablespaces, and are stored in the SYSAUX tablespace.

C. They are collected for all segments and are stored in the SYSAUX tablespace.

D. They are collected for all segments and are stored in the SYSTEM tablespace.

E. They are collected only for the ORDERS table if it has an associated ADO policy.

In your CDB, DEPTCDB, local undo is enabled. You successfully execute this command: FLASHBACK PLUGGABLE DATABASE hrpdb to SCN 424366;

Which action must you perform next?

A. Open HRPDB

B. Take a backup of DEPTCDB

C. Take a backup of HRPDB

D. Open HRPDB with RESETLOGS

E. Open DEPTCDB with RESETLOGS

F. Open DEPTPDB

The In-Memory compression level is set to MEMCOMPRESS FOR QUERY HIGH. For what purpose is this optimized?

A. for parallel query performance only

B. for query performance and space saving

C. for query performance and space saving with greater bias toward space saving

D. for query performance only

E. for space saving only

You create a refreshable copy PDB, PDB_COPY, from the PDB_ORG PDB by using the REFRESH

MODE EVERY 5 MINUTES clause.

What happens every five minutes?

A. An automatic refresh is done provided PDB_COPY is closed; otherwise the refresh is differed and attempted again after five minutes.

B. PDB_COPY is automatically closed provided there is no ongoing transaction in PDB_ORG and then reopened as read-only after the refresh is complete.

C. PDB_COPY'S state is automatically changed to read-write and back to read-only after the refresh is complete.

D. PDB_COPY'S state is automatically changed to read-only and then reopened as read-write after the refresh is complete.

E. An automatic refresh is done provided PDB_COPY is in read-only mode; otherwise the refresh is deferred and attempted again after five minutes.

Which two are true about tables recovered by using the RMAN RECOVER TABLE command or the command itself, starting from Oracle Database 12c Release 2? (Choose two.)

A. They may be recovered to a schema in a different PDB

B. An AUXILIARY instance is started only if there is sufficient disk space

C. They can be recovered by RMAN without an AUXILIARY instance

D. They must be un-partitioned tables

E. They may be recovered to a different schema in the same pluggable database (PDB)

Which three statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose three.)

A. It analyzes only those memory components of the database instance that are impacting performance.

B. It determines the segments on which waits are occurring when the database has a high number of buffer busy waits.

C. It analyzes the performance of the database instance based on the latest two AWR snapshots.

D. It determines SQL statements that are performing poorly.

E. It recommends implementing SQL profiles to generate better execution plans.

F. It analyzes only the components and waits that are impacting database performance.

Which three database objects can have columns stored in the In-Memory (IM) column store? (Choose three.)

A. subpartitions

B. clustered tables

C. materialized views

D. index-organized tables

E. heap tables

F. any SYS owned objects