Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 31:

  Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.

  Only the EMPLOYEE_ID column is indexed.

  Rows exist for employees 100 and 200.

  Examine this statement:

  

  Which two statements are true? (Choose two.)

  A. Employee 100 will have SALARY set to the same value as the SALARY of employee 200

  B. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

  C. Employee 200 will have SALARY set to the same value as the SALARY of employee 100

  D. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200

  E. Employees 100 and 200 will have the same JOB_ID as before the update command

  F. Employees 100 and 200 will have the same SALARY as before the update command

  Correct Answer: AD

• Question 32:

  Which two statements are true about Enterprise Manager Database Express? (Choose two.)

  A. It is available only when the database is open

  B. It can be used to perform database recovery

  C. The same port number can be used for Database Express configurations for databases on different hosts

  D. It can be used to switch a database into ARCHIVELOGMODE

  E. The same port number can be used for multiple Database Express configurations for multiple databases on the same host

  Correct Answer: AC

  A - Correct -> to startup the enterprise manager database express, the database have to be in open state B - Incorrect -> to perform a full recovery, the database has to be in mount state, and EM express not work if database is not in open state C - Correct -> since they are in different hosts, you can use the same port for then (default 5500) D - Incorrect -> to switch a database to ARCHIVELOGMODE the database need to be in mount state (like B answer) E - Incorrect -> the same port cannot be used in the same time for different services in the same host (here a basic of tcp/ip protocol) If need more reference: EM Express (Chapter 3.3) https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/2-day-dba.pdf

  ARCHIVELOGMODE (Chapter 12.3.2) https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/database-administrators-guide.pdf

• Question 33:

  Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three.)

  A. An undo segment may be used by multiple transactions simultaneously

  B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment

  C. Undo segments have a minimum of three extents

  D. Undo segments can extend when a transaction fills the last extent of the undo segment

  E. A single transaction may use multiple undo segments simultaneously

  F. Undo segments must be stored in a BIGFILE tablespace

  G. Undo segments must be stored in a SMALLFILE tablespace

  Correct Answer: DG

  A because of:

  Multiple active transactions can write concurrently to the same undo segment or to different segments. For

  example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes

  to undo segment U2.

• Question 34:

  Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

  A. Duplicates are eliminated automatically by the UNION ALL operator

  B. The number of columns selected in each SELECT statement must be identical

  C. The names of columns selected in each SELECT statement must be identical

  D. The output is sorted by the UNION ALL operator

  E. NULLS are not ignored during duplicate checking

  Correct Answer: BE

• Question 35:

  Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)

  A. The ADR base defaults to $ORACLE_HOME/rdbms/admin if neither DIAGNOSTIC_DEST nor ORACLE_BASE is set

  B. The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the ORACLE_BASE environment variable are not set

  C. It supports diagnostics for Automatic Storage Management (ASM)

  D. It supports diagnostics for Oracle Clusterware

  E. It is held inside an Oracle database schema

  Correct Answer: CD

  The database, Oracle Automatic Storage Management (Oracle ASM), the listener, Oracle Clusterware, and other Oracle products or components store all diagnostic data in the ADR.

  https://docs.oracle.com/database/121/ADMIN/diag.htm#ADMIN11260

• Question 36:

  You want to apply the principle of Least Privilege in all your live databases.

  One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.

  Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose three.)

  A. analysis of all privileges used by all users including administrative users in the database

  B. analysis of all privileges used by all users but excluding administrative users in the database

  C. analysis of privileges that a user has on their own schema objects that they did not use

  D. analysis of privileges that a user has on their own schema objects that they did use

  E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role

  F. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role

  Correct Answer: AEF

  A - Right - We can analyze the administrative user, the oracle just excludes the SYS user B - Wrong, oracle does not delete administrative user, like question A, only SYS user C - Wrong - We cannot analyze the permission of the scheme itself D - Wrong - We cannot analyze the privilege of our schema E - Right - we can analyze the permission that was given directly. F - Right - we can analyze the permission that was given Reference: https://docs.oracle.com/database/121/ARPLS/d_priv_prof.htm#ARPLS74328

• Question 37:

  Examine this command and some partial output:

  

  Why does the DB01.abc.com service show unknown status?

  A. The service DB01.abc.com is dynamically registered

  B. The LOCAL_LISTENER database parameter is not set to a service name that refers to LISTENER_1

  C. The service DB01.abc.com is statically registered

  D. The listener is not listening on the default port 1521

  E. The SID_LIST_LISTENER section is not contained in the LISTENER.ORA file

  Correct Answer: C

  An UNKNOWN status means that the instance is registered statically (with a SID_LIST) in the listener. ora file rather than dynamically with service registration. Therefore, the database status is "not known" to the listener.

• Question 38:

  Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)

  A. The Oracle Net Configuration Assistant is only used when running the Oracle installer

  B. Oracle Net Manager can be used to centrally configure listeners on any database server target

  C. The lsnrctl utility requires a listener.ora file to exist before it is started

  D. Oracle Net Manager can be used to locally configure naming methods on a database server

  E. Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server

  F. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target

  Correct Answer: DEF

  Oracle Net Manager to locally control and Enterprise Manager Cloud Control to centrally control. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/introducing-oracle-netservices.html#GUID-6B1F0584-887D-4D2A-B982-40ACF15DF985

  1.

  Oracle Enterprise Manager Cloud Control combines configuration functionality across multiple file systems, along with listener administrative control to provide an integrated environment for configuring and managing Oracle Net Services.

  2.

  Oracle Net Manager provides configuration functionality for an Oracle home on a local client or server host.

• Question 39:

  In your data center, Oracle Managed Files (OMF) is used for all databases.

  All tablespaces are smallfile tablespaces.

  SALES_Q1 is a permanent user-defined tablespace in the SALES database.

  Examine this command which is about to be issued by a DBA logged in to the SALES database:

  ALTER TABLESPACE sales_q1 ADD DATAFILE;

  Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)

  A. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.

  B. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.

  C. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.

  D. Add the AUTOEXTEND ON clause with NEXT set to 100M.

  E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.

  Correct Answer: BC

  https://oracle-base.com/articles/9i/oracle-managed-files

  -Files typically have a default size of 100M

  - if OMF is used you do not need to specify a path for the new created datafile BUT we have to "ensure that the command executes successfully"

• Question 40:

  Examine the description of the EMPLOYEES table:

  

  Which query is valid?

  A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

  B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

  C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

  D. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

  Correct Answer: C