Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 131:

  In your Oracle 12c database, you create two roles: AUDITOR and AUDIT_ADMIN. You then execute the following commands:

  

  To create a privilege analysis policy for the AUDIT_ADMIN role, you execute the following PL/SQL block:

  

  Which statement is correct about the procedure?

  A. It always captures the use of the CREATE VIEW and CREATE TABLE privileges.
  B. It only captures the use of the privileges used through the AUDIT_ADMIN role when the ACCOUNT PAYABLE module is used.
  C. It always captures the use of the privileges exercised through the AUDITOR and AUDIT_ADMIN roles.
  D. It captures the use of the privileges used through the AUDIT_ADMIN and AUDITOR roles when the ACCOUNT PAYABLE module is used.
  B. It only captures the use of the privileges used through the AUDIT_ADMIN role when the ACCOUNT PAYABLE module is used.

  ---

  References: https://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591

• Question 132:

  You have altered a non-unique index to be invisible to determine if queries execute within an acceptable response time without using this index. Which two are possible if table updates are performed which affect the invisible index columns? (Choose two.)

  A. The index remains invisible.
  B. The index is not updated by the DML statements on the indexed table.
  C. The index automatically becomes visible in order to have it updated by DML on the table.
  D. The index becomes unusable but the table is updated by the DML.
  E. The index is updated by the DML on the table.
  A. The index remains invisible.
  E. The index is updated by the DML on the table.

  ---

  Unlike unusable indexes, an invisible index is maintained during DML statements. Note:

  * Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

• Question 133:

  Your database is running in ARCHIVELOG mode. You back up the control file by executing ALTER DATABASE BACKUP CONTROL TO TRACE NORESETLOGS, whenever you make structural changes to the database. A media failure has damaged all the control files, but the online redo log files are intact. You issue a SHUTDOWN ABORT.

  How should you recover the control file to its default location?

  A. Restore the backup control file to all the locations specified in the CONTROL_FILES parameter file, start a new instance and mount the database, recover the database, and then open the database with the RESETLOGS option.
  B. Issue the STARTUP NOMOUNT command, restore the backup control file only to the default location, issue RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL, and open the database with the RESETLOGS option.
  C. Issue the STARTUP MOUNT command, restore the backup control file to all the locations specified in the CONTROL_FILES parameter file, and then issue RECOVER DATABASE USING BACKUP CONTROLFILE.
  D. Issue the STARTUP NOMOUNT command, re-create the control file manually by using the CREATE CONTROLFILE command, issue the RECOVER DATABASE command, and then issue the ALTER DATABASE OPEN command.
  B. Issue the STARTUP NOMOUNT command, restore the backup control file only to the default location, issue RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL, and open the database with the RESETLOGS option.

  ---

  Reference: https://docs.oracle.com/cd/E28271_01/backup.1111/e10642/osadvsce.htm

• Question 134:

  What is the effect of specifying the "ENABLE PLUGGABLE DATABASE" clause in a "CREATE DATABASE" statement?

  A. It will create a multitenant container database (CDB) with only the root opened.
  B. It will create a CDB with root opened and seed read only.
  C. It will create a CDB with root and seed opened and one PDB mounted.
  D. It will create a CDB that must be plugged into an existing CDB.
  E. It will create a CDB with root opened and seed mounted.
  B. It will create a CDB with root opened and seed read only.

  ---

  *

  The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.

  Along with the root (CDB$ROOT), Oracle Database automatically creates a seed PDB (PDB$SEED). The following graphic shows a newly created CDB:

  *

  Creating a PDB

  Rather than constructing the data dictionary tables that define an empty PDB from scratch, and then populating its Obj$ and Dependency$ tables, the empty PDB is created when the CDB is created. (Here, we use empty to mean containing

  no customer-created artifacts.) It is referred to as the seed PDB and has the name PDB$Seed. Every CDB non-negotiably contains a seed PDB; it is non-negotiably always open in read-only mode. This has no conceptual significance; rather,

  it is just an optimization device. The create PDB operation is implemented as a special case of the clone PDB operation.

  

• Question 135:

  You notice a high number of waits for the db file scattered read and db file sequential read events in the recent Automatic Database Diagnostic Monitor (ADDM) report. After further investigation, you find that queries are performing too many full table scans and indexes are not being used even though the filter columns are indexed.

  Identify three possible reasons for this.

  A. Missing or stale histogram statistics
  B. Undersized shared pool
  C. High clustering factor for the indexes
  D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
  E. Oversized buffer cache
  A. Missing or stale histogram statistics
  C. High clustering factor for the indexes
  D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter

  ---

  D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

• Question 136:

  In your multitenant container database (CDB) containing same pluggable databases (PDBs), you execute the following commands in the root container:

  

  Which two statements are true?

  A. The C # # ROLE1 role is created in the root database and all the PDBs.
  B. The C # # ROLE1 role is created only in the root database because the container clause is not used.
  C. Privileges are granted to the C##A_ADMIN user only in the root database.
  D. Privileges are granted to the C##A_ADMIN user in the root database and all PDBs.
  E. The statement for granting a role to a user fails because the CONTAINER clause is not used.
  A. The C # # ROLE1 role is created in the root database and all the PDBs.
  C. Privileges are granted to the C##A_ADMIN user only in the root database.

  ---

  * You can include the CONTAINER clause in several SQL statements, such as the CREATE USER, ALTER USER, CREATE ROLE, GRANT, REVOKE, and ALTER SYSTEM statements.

  * * CREATE ROLE with CONTAINER (optional) clause

  / CONTAINER = ALL

  Creates a common role.

  / CONTAINER = CURRENT

  Creates a local role in the current PDB.

• Question 137:

  Which three statements are true about SQL plan directives?

  A. They are tied to a specific statement or SQL ID.
  B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
  C. They are used to gather only missing statistics.
  D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
  E. They instruct the optimizer to create only column group statistics.
  F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.
  B. They instruct the maintenance job to collect missing statistics or perform dynamic sampling to generate a more optimal plan.
  D. They are created for a query expression where statistics are missing or the cardinality estimates by the optimizer are incorrect.
  F. Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX tablespace.

• Question 138:

  On your Oracle 12c 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. The optimizer evaluates index access from both the indexes before deciding on which index to use for a query execution plan.
  B. Both the indexes are created: however, only ORD_CUSTOMER_IX2 index is visible.
  C. Only the ORD_CUSTOMER_IX1 index is created.
  D. Only the ORD_CUSTOMER_IX2 index is created.
  E. Both the indexes are updated when a new row is inserted, updated, or deleted in the ORDERS table.
  B. Both the indexes are created: however, only ORD_CUSTOMER_IX2 index is visible.
  E. Both the indexes are updated when a new row is inserted, updated, or deleted in the ORDERS table.

• Question 139:

  You create a new pluggable database, HR_PDB, from the seed database.

  Which three tablespaces are created by default in HR_PDB? (Choose three.)

  A. SYSTEM
  B. SYSAUX
  C. EXAMPLE
  D. UNDO
  E. TEMP
  F. USERS
  A. SYSTEM
  B. SYSAUX
  E. TEMP

  ---

  *

  A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces. It can also contains other user created tablespaces in it. *

  *

  Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every database.

  *

  tablespace_datafile_clauses

  Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX tablespaces in the seed PDB.

  Incorrect Answers:

  D: a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB.

  Note:

  * Example: CONN pdb_admin@pdb1

  SELECT tablespace_name FROM dba_tablespaces;

  TABLESPACE_NAME

  SYSTEM SYSAUX TEMP USERS

  SQL>

• Question 140:

  Which statement is true concerning compression during an Online Datafile Move?

  A. Only tables may be compressed during the Online Datafile Move.
  B. The DBA may choose which objects in the datafile to be moved get compressed.
  C. All objects in a data file to be moved will always to be compressed.
  D. Uncompressed data files may be moved online to compress the entire file with Hybrid Columnar Compression.
  E. If an object is already compressed in the source location, then the compression type for that object remains unchanged after the move.
  A. Only tables may be compressed during the Online Datafile Move.