Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 101:

  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 TOP-N query is created during execution and a filter is added to the query dynamically using ROWNUM to limit the rows to 20 percent of the total rows.
  B. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.
  C. 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.
  D. A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the total rows.
  B. A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.

• Question 102:

  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
  B. to support synchronization of a standby database with the primary database in a Data environment
  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

  ---

  *

  With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

  *

  RMAN uses a recovery catalog to track filenames for all database files in a Data Guard environment. A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. The catalog also records where the online redo logs, standby redo logs, tempfiles, archived redo logs, backup sets, and image copies are created.

• Question 103:

  You must track all transactions that modify certain tables in the SALES schema for at least three years.

  Automatic undo management is enabled for the database with a retention of one day.

  Which two must you do to track the transactions? (Choose two.)

  A. Enable supplemental logging for the database.
  B. Specify undo retention guarantee for the database.
  C. Create a Flashback Data Archive in the tablespace where the tables are stored.
  D. Create a Flashback Data Archive in any suitable tablespace.
  E. Enable Flashback Data Archiving for the tables that require tracking.
  D. Create a Flashback Data Archive in any suitable tablespace.
  E. Enable Flashback Data Archiving for the tables that require tracking.

  ---

  E: By default, flashback archiving is disabled for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.

  D: Creating a Flashback Data Archive / Create a Flashback Data Archive with the CREATE FLASHBACK ARCHIVE statement, specifying the following: Name of the Flashback Data Archive Name of the first tablespace of the Flashback Data Archive

  (Optional) Maximum amount of space that the Flashback Data Archive can use in the first tablespace

  / Create a Flashback Data Archive named fla2 that uses tablespace tbs2, whose data will be retained for two years:

  CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;

• Question 104:

  Which two are true concerning a multitenant container database with three pluggable database?

  A. All administration tasks must be done to a specific pluggable database.
  B. The pluggable databases increase patching time.
  C. The pluggable databases reduce administration effort.
  D. The pluggable databases are patched together.
  E. Pluggable databases are only used for database consolidation.
  C. The pluggable databases reduce administration effort.
  E. Pluggable databases are only used for database consolidation.

  ---

  The benefits of Oracle Multitenant are brought by implementing a pure deployment choice. The following list calls out the most compelling examples.

  *

  High consolidation density. (E)

  The many pluggable databases in a single multitenant container database share its memory and background processes, letting you operate many more pluggable databases on a particular platform than you can single databases that use the

  old architecture. This is the same benefit that schema-based consolidation brings.

  *

  Rapid provisioning and cloning using SQL.

  *

  New paradigms for rapid patching and upgrades. (D, not B)

  The investment of time and effort to patch one multitenant container database results in patching all of its many pluggable databases. To patch a single pluggable database, you simply unplug/plug to a multitenant container database at a

  different Oracle Database software version.

  *

  (C, not A) Manage many databases as one.

  By consolidating existing databases as pluggable databases, administrators can manage many databases as one. For example, tasks like backup and disaster recovery are performed at the multitenant container database level.

  *

  Dynamic between pluggable database resource management. In Oracle Database 12c, Resource Manager is extended with specific functionality to control the competition for resources between the pluggable databases within a multitenant

  container database.

  Note:

  *

  Oracle Multitenant is a new option for Oracle Database 12c Enterprise Edition that helps customers reduce IT costs by simplifying consolidation, provisioning, upgrades, and more. It is supported by a new architecture that allows a

  multitenant container database to hold many pluggable databases. And it fully complements other options, including Oracle Real Application Clusters and Oracle Active Data Guard. An existing database can be simply adopted, with no

  change, as a pluggable database; and no changes are needed in the other tiers of the application.

• Question 105:

  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
  A. Change in the Oracle Database version
  C. Change in the hardware configuration of the database server
  E. Database and operating system upgrade

  ---

  Oracle 11g/12c makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

  (E) Database, operating system, or hardware upgrades.

  (A, C) Database, operating system, or hardware configuration changes.

  Database initialization parameter changes.

  Schema changes, such as adding indexes or materialized views.

  Refreshing optimizer statistics.

  Creating or changing SQL profiles.

• Question 106:

  In your multitenant container database (CDB) that contains pluggable databases (PDBs), the HR user executes the following commands to create and grant privileges on a procedure:

  

  How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from inserting values into tables on which they do not have any privileges?

  A. Create the CREATE_TEST procedure with definer's rights.
  B. Grant the EXECUTE privilege to users with GRANT OPTION on the CREATE_TEST procedure.
  C. Create the CREATE_TEST procedure with invoker's rights.
  D. Create the CREATE_TEST procedure as part of a package and grant users the EXECUTE privilege the package.
  C. Create the CREATE_TEST procedure with invoker's rights.

  ---

  If a program unit does not need to be executed with the escalated privileges of the definer, you should specify that the program unit executes with the privileges of the caller, also known as the invoker. Invoker's rights can mitigate the risk of SQL injection.

  Incorrect Answers:

  A: By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer-rights subprograms are bound to the schema in which they reside. not B: Using the GRANT option, a user can grant an Object privilege to another user or to PUBLIC.

• Question 107:

  Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:

  

  Which two statements are true about redefining the table? (Choose two.)

  A. All the triggers for the table are disabled without changing any of the column names or column types in the table.
  B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.
  C. VPD policies are copied from the original table to the new table during online redefinition.
  D. You must copy the VPD policies manually from the original table to the new table during online redefinition.
  A. All the triggers for the table are disabled without changing any of the column names or column types in the table.
  C. VPD policies are copied from the original table to the new table during online redefinition.

  ---

  The triggers cloned to the interim table are disabled until the redefinition is completed. Once the redefinition is complete, all cloned objects are renamed to the original names used by they objects they were cloned from. References: http://www.oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1.php

• Question 108:

  Which three operations can be performed as multipartition operations in Oracle Database 12c? (Choose three.)

  A. Merge partitions of a list partitioned table.
  B. Drop partitions of a list partitioned table.
  C. Coalesce partitions of a hash-partitioned global index.
  D. Move partitions of a range-partitioned table.
  E. Rename partitions of a range partitioned table.
  F. Merge partitions of a reference partitioned index.
  A. Merge partitions of a list partitioned table.
  B. Drop partitions of a list partitioned table.
  F. Merge partitions of a reference partitioned index.

  ---

  Multipartition maintenance enables adding, dropping, truncate, merge, split operations on multiple partitions.

  A: Merge Multiple Partitions:

  The new "ALTER TABLE ... MERGE PARTITIONS " help merge multiple partitions or subpartitions with a single statement. When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

  B: Drop Multiple Partitions:

  The new "ALTER TABLE ... DROP PARTITIONS " help drop multiple partitions or subpartitions with a single statement.

  Example:

  view plaincopy to clipboardprint?

  SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS

  Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;

  Table altered

  SQL>

  Restrictions :

  You can't drop all partitions of the table.

  If the table has a single partition, you will get the error: ORA-14083: cannot drop the only partition of a partitioned.

• Question 109:

  In your Database, the TBS PERCENT USED parameter is set to 60 and the TBS PERCENT FREE parameter is set to 20.

  Which two storage-tiering actions might be automated when using information Lifecycle Management (ILM) to automate data movement?

  A. The movement of all segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
  B. Setting the target tablespace to read-only
  C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
  D. Setting the target tablespace offline
  E. The movement of some blocks to a target tablespace with a lower degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED
  B. Setting the target tablespace to read-only
  C. The movement of some segments to a target tablespace with a higher degree of compression, on a different storage tier, when the source tablespace exceeds TBS PERCENT USED

  ---

  The value for TBS_PERCENT_USED specifies the percentage of the tablespace quota when a tablespace is considered full. The value for TBS_PERCENT_FREE specifies the targeted free percentage for the tablespace. When the percentage of the tablespace quota reaches the value of TBS_PERCENT_USED, ADO begins to move data so that percent free of the tablespace quota approaches the value of TBS_PERCENT_FREE. This action by ADO is a best effort and not a guarantee.

• Question 110:

  You are administering a database and you receive a requirement to apply the following restrictions:

  1.

  A connection must be terminated after four unsuccessful login attempts by user.

  2.

  A user should not be able to create more than four simultaneous sessions.

  3.

  User session must be terminated after 15 minutes of inactivity.

  4.

  Users must be prompted to change their passwords every 15 days. How would you accomplish these requirements?

  A. by granting a secure application role to the users
  B. by creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT parameter to FALSE
  C. By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4
  D. By Implementing Fine-Grained Auditing (FGA) and setting the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.
  E. By implementing the database resource Manager plan and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameters to 4.
  C. By creating and assigning a profile to the users and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter to 4

  ---

  SEC_MAX_FAILED_LOGIN_ATTEMPTS specifies the number of authentication attempts that can be made by a client on a connection to the server process. These login attempts can be for multiple user accounts in the same connection. After the specified number of failure attempts, the connection will be automatically dropped by the server process.

  References: https://docs.oracle.com/database/121/REFRN/GUID-DEC2A3B2-F49B-499E-A3CF-D097F3A5BA83.htm#REFRN10274