Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 151:

  You execute a DROP USER CASCADE on an Oracle 11g release 1 database and immediately realized that you forgot to copy the OCA.EXAM_RESULTS table to the OCP schema.

  RECYCLE_BIN was enabled before the DROP USER was executed and the OCP user has been granted the FLASHBACK ANY TABLE system privilege.

  What is the quickest way to recover the contents of the OCA.EXAM_RESULTS table to the OCP schema?

  A. Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO OCP.EXAM_RESULTS; connected as SYSTEM.
  B. Recover the table using traditional Tablespace Point In Time Recovery.
  C. Recovery the table using Database Point In Time Recovery.
  D. Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO EXAM_RESULTS; connected as the OCP user.
  D. Execute FLASHBACK TABLE OCA.EXAM_RESULTS TO BEFORE DROP RENAME TO EXAM_RESULTS; connected as the OCP user.

• Question 152:

  Which two statements are true about Oracle Managed Files (OMF)? (Choose two.)

  A. OMF cannot be used in a database that already has data files created with user-specified directions.
  B. The file system directions that are specified by OMF parameters are created automatically.
  C. OMF can be used with ASM disk groups, as well as with raw devices, for better file management.
  D. OMF automatically creates unique file names for table spaces and control files.
  E. OMF may affect the location of the redo log files and archived log files.
  D. OMF automatically creates unique file names for table spaces and control files.
  E. OMF may affect the location of the redo log files and archived log files.

  ---

  D: The database internally uses standard file system interfaces to create and delete files as needed for the following database structures: Tablespaces Redo log files Control files Archived logs Block change tracking files Flashback logs RMAN backups

  Note:

  * Using Oracle-managed files simplifies the administration of an Oracle Database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files that make up an Oracle Database. With Oracle-managed files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's datafile with the DATAFILE clause.

  References: http://www.oracle-base.com/articles/9i/oracle-managed-files.php http://docs.oracle.com/cd/B10500_01/server.920/a96521/omf.htm

• Question 153:

  A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with SQL:

  

  There is enough free space in the disk group for mirroring to be done.

  What happens if the CONTROLLER1 failure group becomes unavailable due to error or for maintenance?

  A. Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1 will fall.
  B. Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the CONTROLLER1 for failure group is brought back online.
  C. The data in the CONTROLLER1 failure group is copied to the CONTROLLER2 failure group and rebalancing is initiated.
  D. ASM does not mirror any data until the CONTROLLER1 failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the CONTROLLER2 failure group, without mirroring.
  D. ASM does not mirror any data until the CONTROLLER1 failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the CONTROLLER2 failure group, without mirroring.

• Question 154:

  Examine this set of commands to perform data pump export and import operations:

  On the source database:

  $> expdp sh/sh DIRECTORY=dpdir DUMPFILE=sale1.dmp VIEWS_AS_TABLE=sales_q1

  On the target database:

  $> impdp sh/sh DIRECTORY=dpdir DUMPFILE=sale1.dmp VIEWS_AS_TABLE=sales_q1

  Which three statements are true? (Choose three.)

  A. This impdp operation creates the view as a table and populates it with data from the export dump file.
  B. The exdp operation exports data that satisfies the conditions of the defining query used when the view was created.
  C. Metadata for dependent objects required to create the SALES_Q1 view is exported.
  D. All rows from the dependent objects that are required are exported along with the metadata to create the SALES_Q1 view.
  E. The impdp operation creates the view and the dependent object.
  A. This impdp operation creates the view as a table and populates it with data from the export dump file.
  B. The exdp operation exports data that satisfies the conditions of the defining query used when the view was created.
  E. The impdp operation creates the view and the dependent object.

• Question 155:

  Identify two correct statements about multitenant architecture. (Choose two.)

  A. Multitenant architecture can be deployed only in a Real Application Clusters (RAC) configuration.
  B. Multiple pluggable databases (PDBs) share the SGA of a multitenant container database (CDB).
  C. Multiple CDBs share certain PDB resources.
  D. Multiple non-RAC CDB instances can mount the same PDB as long as they are on the same server.
  E. Patches are always applied at the CDB level.
  F. A PDB can have a private undo tablespace.
  B. Multiple pluggable databases (PDBs) share the SGA of a multitenant container database (CDB).
  E. Patches are always applied at the CDB level.

  ---

  B: Using 12c Resource manager you will be able control CPU, Exadata I/O, sessions and parallel servers. A new 12c CDB Resource Manager Plan will use so-called "Shares" (resource allocations) to specify how CPU is distributed between

  PDBs. A CDB Resource Manager Plan also can use "utilization limits" to limit the CPU usage for a PDB. With a default directive, you do not need to modify the resource plan for each PDB plug and unplug.

  E: New paradigms for rapid patching and upgrades.

  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.

  Incorrect Answers:

  A:

  *

  The Oracle RAC documentation describes special considerations for a CDB in an Oracle RAC environment.

  *

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

  D: You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.

  F:

  *

  UNDO tablespace can NOT be local and stays on the CDB level.

  *

  Redo and undo go hand in hand, and so the CDB as a whole has a single undo tablespace per RAC instance.

• Question 156:

  Examine the following query output: You issue the following command to import tables into the hr schema:

  

  $ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y

  Which statement is true?

  A. All database operations performed by the impdp command are logged.
  B. Only CREATE INDEX and CREATE TABLE statements generated by the import are logged.
  C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.
  D. None of the operations against the master table used by Oracle Data Pump to coordinate its activities are logged.
  C. Only CREATE TABLE and ALTER TABLE statements generated by the import are logged.

  ---

  Note from Oracle Documentation:

  With redo logging disabled, the disk space required for redo logs during an Oracle Data Pump import will be smaller. However, to ensure recovery from media failure, the DBA should do an RMAN backup after the import completes. Even with

  this parameter specified, there is still redo logging for other operations of Oracle Data Pump. This includes all CREATE and ALTER statements, except CREATE INDEX, and all operations against the master table used by Oracle Data Pump

  during the import.

• Question 157:

  You executed this command to create a password file:

  $ orapwd file = orapworcl entries = 10 ignorecase = N

  Which two statements are true about the password file?

  A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER role.
  B. It contains username and passwords of database users who are members of the OSOPER operating system group.
  C. It contains usernames and passwords of database users who are members of the OSDBA operating system group.
  D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.
  E. It will not permit the use of mixed case passwords for the database users who have been granted the SYSDBA role.
  A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER role.
  D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.

  ---

  *

  You can create a password file using the password file creation utility, ORAPWD.

  *

  Adding Users to a Password File

  When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

  A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

  *

  The syntax of the ORAPWD command is as follows:

  ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]

  *

  IGNORECASE

  If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

• Question 158:

  You administer an online transaction processing (OLTP) system whose database is stored in Automatic Storage Management (ASM) and whose disk group use normal redundancy.

  One of the ASM disks goes offline, and is then dropped because it was not brought online before DISK_REPAIR_TIME elapsed.

  When the disk is replaced and added back to the disk group, the ensuing rebalance operation is too slow.

  Which two recommendations should you make to speed up the rebalance operation if this type of failure happens again?

  A. Increase the value of the ASM_POWER_LIMIT parameter.
  B. Set the DISK_REPAIR_TIME disk attribute to a lower value.
  C. Specify the statement that adds the disk back to the disk group.
  D. Increase the number of ASMB processes.
  E. Increase the number of DBWR_IO_SLAVES in the ASM instance.
  A. Increase the value of the ASM_POWER_LIMIT parameter.
  C. Specify the statement that adds the disk back to the disk group.

  ---

  ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.

  Grouping operations in a single ALTER DISKGROUP statement can reduce rebalancing operations. References: http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG10070

• Question 159:

  Examine the parameter for your database instance:

  

  You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:

  

  Identify the reason why the optimizer chose different execution plans.

  A. The optimizer used a dynamic plan for the query.
  B. The optimizer chose different plans because automatic dynamic sampling was enabled.
  C. The optimizer used re-optimization cardinality feedback for the query.
  D. The optimizer chose different plan because extended statistics were created for the columns used.
  B. The optimizer chose different plans because automatic dynamic sampling was enabled.

  ---

  * optimizer_dynamic_sampling OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics. Range of values0 to 11

• Question 160:

  You execute the following piece of code with appropriate privileges:

  

  User SCOTT has been granted the CREATE SESSION privilege and the MGR role.

  Which two statements are true when a session logged in as SCOTT queries the SAL column in the view and the table?

  A. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
  B. Data is redacted for EMP.SAL column only if the SCOTT session has the MGR role set.
  C. Data is never redacted for the EMP_V.SAL column.
  D. Data is redacted for the EMP_V.SAL column only if the SCOTT session has the MGR role set.
  E. Data is redacted for the EMP_V.SAL column only if the SCOTT session does not have the MGR role set.
  A. Data is redacted for the EMP.SAL column only if the SCOTT session does not have the MGR role set.
  C. Data is never redacted for the EMP_V.SAL column.

  ---

  Note:

  *

  DBMS_REDACT.FULL completely redacts the column data.

  *

  DBMS_REDACT.NONE applies no redaction on the column data. Use this function for development testing purposes. LOB columns are not supported.

  *

  The DBMS_REDACT package provides an interface to Oracle Data Redaction, which enables you to mask (redact) data that is returned from queries issued by low-privileged users or an application.

  *

  If you create a view chain (that is, a view based on another view), then the Data Redaction policy also applies throughout this view chain. The policies remain in effect all of the way up through this view chain, but if another policy is created for one of these views, then for the columns affected in the subsequent views, this new policy takes precedence.