Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 91:

  Examine the description of the PRODUCTS table:

  

  Which query is valid?

  A. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
  B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;
  C. SELECT prod_id, AVG(MAX(cost)) FROM products GROUP BY prod_id;
  D. SELECT prod_id, MAX(AVG(cost)) FROM products GROUP BY prod_id;
  B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;

  ---

  Explanation/Reference:

• Question 92:

  Which three statements are true regarding single row subqueries?

  A. They must be placed on the right side of the comparison operator or condition.
  B. They must be placed on the left side of the comparison operator or condition.
  C. They can be used in the where clause.
  D. A SQL statement may have multiple single row subquery blocks.
  E. They must return a row to prevent errors in the SQL statement.
  F. They can be used in the having clause.
  C. They can be used in the where clause.
  D. A SQL statement may have multiple single row subquery blocks.
  F. They can be used in the having clause.

• Question 93:

  Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)

  A. They can be written by server processes
  B. Trace files are written to the Fast Recovery Area (FRA)
  C. They can be written by background processes
  D. All trace files contain error information that require contacting Oracle Support
  E. Trace file names are based on the database name concatenated with a sequential number
  A. They can be written by server processes
  C. They can be written by background processes

  ---

  Explanation/Reference:

  Reference: https://gerardnico.com/db/oracle/trace_file https://docs.oracle.com/html/E25494_01/monitoring001.htm

  Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, and other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.

• Question 94:

  Which two statements are true about UNDO and REDO? (Choose two.)

  A. The generation of UNDO generates REDO
  B. DML modifies Oracle database objects and only generates UNDO
  C. The generation of REDO generates UNDO
  D. DML modifies Oracle database objects and only generates REDO
  E. DML modifies Oracle database objects and generates UNDO and REDO
  A. The generation of UNDO generates REDO
  E. DML modifies Oracle database objects and generates UNDO and REDO

  ---

  Explanation/Reference:

  ... undo is stored in redo logs also. During roll forward phase of instance/media recovery, as changes (redo in redo logs) are applied to datafiles, undo stored in redo logs is used to generate undo segments. These segments are subsequently used to rollback uncommitted changes during rollback phase of instance/media recovery.

  https://www.experts-exchange.com/articles/13880/UNDO-AND-REDO-IN-ORACLE.html

• Question 95:

  Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)

  A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods
  B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
  C. Update operations always attempt to find blocks with free space appropriate to the length of the row being updated
  D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
  E. A block will always be eligible for inserts if the row is short enough to fit into the block
  B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
  D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted

  ---

  Explanation/Reference:

  Characteristics of Bitmap Segment Management

  Bitmap space management uses four bits inside each data block header to indicate the amount of available space in the data block. Unlike traditional space management with a fixed relink and unlink threshold, bitmap space managements allow Oracle to compare the actual row space for an INSERT with the actual available space on the data block. This enables better reuse of the available free space especially for objects with rows of highly varying size. Here are the values inside the four-bit space:

  1.

  Value Meaning

  2.

  0000 Unformatted Block

  3.

  0001 Block is logically full

  4.

  0010 <25% free space

  5.

  0011 >25% but <50% free space

  6.

  0100 > 50% but <75% free space

  7.

  0101 >75% free space Table 1: Bitmap value meanings.

• Question 96:

  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
  B. analysis of all privileges used by all users but excluding administrative users in the database
  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

  ---

  Explanation/Reference:

• Question 97:

  The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE.

  NLS_DATE_FORMAT is set to DD-MON-RR.

  Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

  A. CONCAT (qty_sold, invoice_date) : requires explicit conversion
  B. invoice_date = '15-march-2019' : uses implicit conversion
  C. invoie_date > '01-02-2019' : uses implicit conversion
  D. qty_sold BETWEEN `101' AND '110' : uses implicit conversion
  E. qty_sold = `0554982' uses implicit conversion
  B. invoice_date = '15-march-2019' : uses implicit conversion
  D. qty_sold BETWEEN `101' AND '110' : uses implicit conversion

  ---

  Explanation/Reference:

• Question 98:

  Examine the description of the employees table:

  

  Which query requires explicit data type conversion?

  A. SELECT join_date FROM employees WHERE join_date > '10-02-2018';
  B. SELECT join_date + '20' FROM employees;
  C. SELECT salary + '120.50' FROM employees;
  D. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
  E. SELECT join_date || ' ' || salary FROM employees;
  A. SELECT join_date FROM employees WHERE join_date > '10-02-2018';

  ---

  Explanation/Reference:

• Question 99:

  You execute this command:

  

  Sufficient storage is available in filesystem /u01.

  Which two statements are true about the BIG_TBS tablespace? (Choose two.)

  A. AUTOEXTEND is possible for the datafile
  B. It must be bigger than the largest SMALLFILE tablespace
  C. Additional data files may not be added
  D. It will be a dictionary-managed tablespace by default
  E. It will always have a 32K blocksize
  A. AUTOEXTEND is possible for the datafile
  C. Additional data files may not be added

• Question 100:

  You execute this command:

  CREATE SMALLFILE TABLESPACE sales DATAFILE `/u01/app/oracle/sales01.dbf SIZE 5G SEGMENT SPACE MANAGEMENT AUTO;

  Which two statements are true about the SALES tablespace? (Choose two.)

  A. It must be smaller than the smallest BIGFILE tablespace
  B. Free space is managed using freelists
  C. Any data files added to the tablespace must have a size of 5 gigabytes
  D. It uses the database default blocksize
  E. It is a locally managed tablespace
  D. It uses the database default blocksize
  E. It is a locally managed tablespace

  ---

  Explanation/Reference:

  Incorrect segment management clause It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace. AUTO :Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. MANUAL :Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists

  E - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11360 Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces,

  D - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/create005.htm#ADMIN11105 The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific.