Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 11:

  Examine the parameters for your database instance:

  

  Which three statements are true about the process of automatic optimization by using cardinality feedback?

  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  B. The optimizer can re optimize a query only once using cardinality feedback.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
  E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.

  ---

  C: During the first execution of a SQL statement, an execution plan is generated as usual.

  D: if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback. (not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions. optimizer_dynamic_sampling

  optimizer_features_enable

  *

  dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. Note:

  *

  OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. Range of values. 0 to 10

  *

  Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

• Question 12:

  Which three statements are true about using flashback database in a multitenant container database (CDB)?

  A. The root container can be flashed back without flashing back the pluggable databases (PDBs).
  B. To enable flashback database, the CDB must be mounted.
  C. Individual PDBs can be flashed back without flashing back the entire CDB.
  D. The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable flashback of the CDB.
  E. A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore point.
  B. To enable flashback database, the CDB must be mounted.
  D. The DB_FLASHBACK RETENTION_TARGET parameter must be set to enable flashback of the CDB.
  E. A CDB can be flashed back specifying the desired target point in time or an SCN, but not a restore point.

• Question 13:

  Which three statements are true about adaptive SQL plan management?

  A. It adds new; bettor plans automatically as fixed plans to the baseline.
  B. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
  C. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
  D. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
  B. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
  C. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
  D. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.

• Question 14:

  Your multitenant container database (CDB) contains pluggable databases (PDBs), you are connected to the PDB HR_PDB. You execute the following command:

  

  What is the result?

  A. It executes successfully and creates an UNDO tablespace in HR_PDB.
  B. It falls and reports an error because there can be only one UNDO tablespace in a CDB.
  C. It fails and reports an error because the CONTAINER=ALL clause is not specified in the command.
  D. It fails and reports an error because the CONTAINER=CURRENT clause is not specified in the command.
  E. It executes successfully but neither tablespace nor the data file is created.
  E. It executes successfully but neither tablespace nor the data file is created.

  ---

  Interesting behavior in 12.1.0.1 DB of creating an undo tablespace in a PDB. With the new Multitenant architecture the undo tablespace resides at the CDB level and PDBs all share the same UNDO tablespace. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.

• Question 15:

  What are two benefits of installing Grid Infrastructure software for a stand-alone server before installing and creating an Oracle database? (Choose two.)

  A. Effectively implements role separation
  B. Enables you to take advantage of Oracle Managed Files.
  C. Automatically registers the database with Oracle Restart.
  D. Helps you to easily upgrade the database from a prior release.
  E. Enables the Installation of Grid Infrastructure files on block or raw devices.
  C. Automatically registers the database with Oracle Restart.
  E. Enables the Installation of Grid Infrastructure files on block or raw devices.

  ---

  "Oracle Grid Infrastructure for a standalone server includes Oracle Restart and Oracle Automatic Storage Management. Oracle combined the two infrastructure products into a single set of binaries that is installed into an Oracle Restart home."

  References: http://docs.oracle.com/cd/E16655_01/install.121/e17735/oraclerestart.htm#NTDBI999

• Question 16:

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

  lsnrctl > 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.
  B. They are not affected and continue to function normally.

  ---

  The listener is used when the connection is established. The immediate impact of stopping the listener will be that no new session can be established from a remote host. Existing sessions are not compromised.

• Question 17:

  You are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table's indexes, to another tablespace.

  The table does not have a primary key and is used by an OLTP application.

  Which technique will move the table and indexes while maintaining the highest level of availability to the application?

  A. Oracle Data Pump
  B. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD to move the indexes.
  C. An ALTER TABLE MOVE to move the table and ALTER INDEX REBUILD ONLINE to move the indexes
  D. Online Table Redefinition
  E. Edition-Based Table Redefinition
  D. Online Table Redefinition

  ---

  *

  Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

  *

  To redefine a table online:

  Choose the redefinition method: by key or by rowid

  *

  By key--Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

  *

  By rowid--Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

  You cannot use this method on index-organized tables. Note:

  *

  When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

  Incorrect Answers:

  E: Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

• Question 18:

  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? (Choose two.)

  A. SCOTT, SYS and SYSTEM
  B. SYS and SYSTEM, but not SCOTT
  C. SCOTT, but not SYS and SYSTEM
  D. Only for successful executions
  E. Only for failed executions
  F. Both successful and failed executions
  B. SYS and SYSTEM, but not SCOTT
  F. Both successful and failed executions

• Question 19:

  Which three are amongst the categories into which column definitions are classifies by the Database Migration Assistant for Unicode (DMU)?

  A. alphanumeric columns that will have invalid binary representation after converting to the chosen Unicode character set
  B. alphanumeric columns that need conversion to a different binary representation before converging to the chosen Unicode character set
  C. date columns that will have invalid binary representation after converting to the chosen Unicode character set
  D. numeric columns that will have invalid binary representation after converting to the chosen Unicode character set
  E. numeric columns that will exceed the column size limit after converting to the chosen Unicode character set
  F. alphanumeric columns that need no conversion
  A. alphanumeric columns that will have invalid binary representation after converting to the chosen Unicode character set
  C. date columns that will have invalid binary representation after converting to the chosen Unicode character set
  D. numeric columns that will have invalid binary representation after converting to the chosen Unicode character set

• Question 20:

  Your multitenant container database (CDB) contains some pluggable databases (PDBs), you execute the following command in the root container:

  

  Which two statements are true? (Choose two.)

  A. Schema objects owned by the C# # A_ADMIN common user can be shared across all PDBs.
  B. The C##A_ADMIN user will be able to use the TEMP_TS temporary tablespace only in root.
  C. The command will create a common user whose description is contained in the root and each PDB.
  D. The schema for the common user C # # A_ADMIN can be different in each container.
  E. The command will create a user in the root container only because the CONTAINER clause is not used.
  C. The command will create a common user whose description is contained in the root and each PDB.
  D. The schema for the common user C # # A_ADMIN can be different in each container.