Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 111:

  Which three statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled In UNIX? (Choose three.)

  A. Thread creation must be routed through a dispatcher process
  B. The local listener may spawn a now process and have that new process create a thread
  C. Each Oracle process runs an SCMN thread.
  D. Each multithreaded Oracle process has an SCMN thread.
  E. The local listener may pass the request to an existing process which in turn will create a thread.
  A. Thread creation must be routed through a dispatcher process
  B. The local listener may spawn a now process and have that new process create a thread
  E. The local listener may pass the request to an existing process which in turn will create a thread.

• Question 112:

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

  ---

  * Monitoring database operations Real-Time Database Operations Monitoring enables you to monitor long running database tasks such as batch jobs, scheduler jobs, and Extraction, Transformation, and Loading (ETL) jobs as a composite business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the business operation being monitored. As a DBA or developer, you can define business operations for monitoring by explicitly specifying the start and end of the operation or implicitly with tags that identify the operation.

• Question 113:

  Your database supports a DSS workload that involves the execution of complex queries: Currently, the library cache contains the ideal workload for analysis. You want to analyze some of the queries for an application that are cached in the library cache.

  What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?

  A. Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.
  B. Run the Automatic Workload Repository (AWR) report.
  C. Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.
  D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.
  E. Run the Automatic Database Diagnostic Monitor (ADDM).
  D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

  ---

  *

  SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements.

  *

  The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics.

  Note:

  *

  Decision support system (DSS) workload

  *

  The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles.

• Question 114:

  Examine these two statements: Which three are true about the MRKT tablespace? (Choose three.)

  

  A. The MRKT tablespace is created as a smallfile tablespace, because the file size is less than the minimum required for bigfile files.
  B. The MRKT tablespace may be dropped if it has no contents.
  C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.
  D. No more data files can be added to the tablespace.
  E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.
  C. Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.
  D. No more data files can be added to the tablespace.
  E. The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.

• Question 115:

  On your Oracle Database, you issue the following commands to create indexes:

  SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customer_id, sales_rep_id) INVISIBLE;

  SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);

  Which two statements are true? (Choose two.)

  A. Only the ORD_CUSTOMER_IX1 index created.
  B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
  C. Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the optimizer for queries on the ORDERS table.
  D. The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
  E. Both the indexes are created and used by the optimizer for queries on the ORDERS table.
  F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.
  B. Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS table.
  F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.

  ---

  Not A: Both indexes are created fine.

  B: The invisible index ORD_CUSTOMERS_IX1 and the bitmap index are both updated by DML operations on the Orders table.

  F: Since ORD_CUSTOMERS_IX1 is invisible only ORD_CUSTOMERS_IX2 is used by the query optimizer.

  Incorrect Answers:

  C, D, E:

  * ord_customer_ix1 is an invisible index and is therefore not used by the optimizer.

  * VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the

  parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

  Note: Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap

  corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions,

  such as data warehousing.

• Question 116:

  Which three statements are true about the use of the query result cache? (Choose three.)

  A. Results obtained from a query rewrite to a materialized view may be stored in the query result cache.
  B. Stale results may still be obtained from the query result cache, if the query session's query_rewrite_integrity parameter is set to stale_tolerated.
  C. Results obtained from a join between a table and a view may be stored in the query result cache.
  D. Results from distributed queries are never stored in the query result cache.
  E. Results may not be obtained from the query result cache, when the query_rewrite_enabled parameter is set to false.
  F. Results from remote queries may sometimes be stored in the query result cache.
  A. Results obtained from a query rewrite to a materialized view may be stored in the query result cache.
  B. Stale results may still be obtained from the query result cache, if the query session's query_rewrite_integrity parameter is set to stale_tolerated.
  E. Results may not be obtained from the query result cache, when the query_rewrite_enabled parameter is set to false.

• Question 117:

  The tnsnames.ora file has an entry for the service alias ORCL as follows: The TNSPING command executes successfully when tested with ORCL; however, from the same OS user session, you are not able to connect to the database instance with the following command:

  

  SQL > CONNECT scott/tiger@orcl

  What could be the reason for this?

  A. The listener is not running on the database node.
  B. The TNS_ADMIN environment variable is set to the wrong value.
  C. The orcl.oracle.com database service is not registered with the listener.
  D. The DEFAULT_DOMAIN parameter is set to the wrong value in the sqlnet.ora file.
  E. The listener is running on a different port.
  C. The orcl.oracle.com database service is not registered with the listener.

  ---

  Service registration enables the listener to determine whether a database service and its service handlers are available. A service handler is a dedicated server process or dispatcher that acts as a connection point to a database. During registration, the LREG process provides the listener with the instance name, database service names, and the type and addresses of service handlers. This information enables the listener to start a service handler when a client request arrives.

• Question 118:

  Which three statements are true about Flashback Database? (Choose three.)

  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 resizes flashback logs in the Fast Recovery Area.
  D. Flashback Database can recover a database to the state that it was in before a RESETLOGS 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 before images of blocks, so that redo data may be used to roll forward to the desired flashback time.
  C. The Oracle database automatically creates, deletes, and resizes flashback logs in the Fast Recovery Area.
  D. Flashback Database can recover a database to the state that it was in before a RESETLOGS operation.
  F. Flashback logs are used to restore to the before images of blocks, so that redo data may be used to roll forward to the desired flashback time.

  ---

  Reference: http://docs.oracle.com/database/121/BRADV/flashdb.htm#BRADV529http://docs.oracle.com/database/121/BRADV/glossary.htm#BRADV90169

• Question 119:

  Identify two situations in which the alert log file is updated.

  A. Running a query on a table returns ORA-600: Internal Error.
  B. Inserting a value into a table returns ORA-01722: invalid number.
  C. Creating a table returns ORA-00955: name us already in used by an existing objects.
  D. Inserting a value into a table returns ORA-00001: unique constraint (SYS.OK_TECHP) violated.
  E. Rebuilding an index using ALTER INDEX ... REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.
  A. Running a query on a table returns ORA-600: Internal Error.
  E. Rebuilding an index using ALTER INDEX ... REBUILD fails with an ORA-01578: ORACLE data block corrupted (file # 14, block # 50) error.

  ---

  The alert log is a chronological log of messages and errors, and includes the following items: All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements Messages and errors relating to the functions of shared server and dispatcher processes Errors occurring during the automatic refresh of a materialized view The values of all initialization parameters that had nondefault values at the time the database and instance start

  Note:

  * The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.

• Question 120:

  Which two statements are true about the Oracle Direct Network File system (DNFS)?

  A. It utilizes the OS file system cache.
  B. A traditional NFS mount is not required when using Direct NFS.
  C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.
  D. Direct NFS is available only in UNIX platforms.
  E. Direct NFS can load-balance I/O traffic across multiple network adapters.
  C. Oracle Disk Manager can manage NFS on its own, without using the operating kernel NFS driver.
  E. Direct NFS can load-balance I/O traffic across multiple network adapters.

  ---

  E: Performance is improved by load balancing across multiple network interfaces (if available).

  Note:

  * To enable Direct NFS Client, you must replace the standard Oracle Disk Manager (ODM) library with one that supports Direct NFS Client.

  Incorrect Answers:

  A: Direct NFS Client is capable of performing concurrent direct I/O, which bypasses any operating system level caches and eliminates any operating system write-ordering locks

  B:

  *

  To use Direct NFS Client, the NFS file systems must first be mounted and available over regular NFS mounts.

  *

  Oracle Direct NFS (dNFS) is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP).

  D: Direct NFS is provided as part of the database kernel, and is thus available on all supported database platforms - even those that don't support NFS natively, like Windows.

  Note:

  *

  Oracle Direct NFS (dNFS) is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.

  *

  Oracle Direct NFS (dNFS) is an internal I/O layer that provides faster access to large NFS files than traditional NFS clients.