Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 171:

  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;
  C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

• Question 172:

  Examine these commands:

  

  Which two statements are true about the sqiidr execution?

  A. It generates a sqi script that it uses to load data from EMP.DAT TO EMP.
  B. It overwrites the data for Alan and adds data for Curl and Bob
  C. It appends data from EMP.DAT TO EMP.
  D. It overwrites all data in emp with data from emp.dat.
  E. It generates a log that contains control file entries, which can be used with normal SQL'Loader operations.
  A. It generates a sqi script that it uses to load data from EMP.DAT TO EMP.
  C. It appends data from EMP.DAT TO EMP.

• Question 173:

  Which two statements are true about single row functions? (Choose two.)

  A. MOD : returns the remainder of a division operation.
  B. FLOOR : returns the smallest integer greater than or equal to a specified number.
  C. TRUNC : can be used only with NUMBER data types.
  D. CONCAT : can be used to combine any number of values
  E. CEIL : can be used for positive and negative numbers
  C. TRUNC : can be used only with NUMBER data types.
  E. CEIL : can be used for positive and negative numbers

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Single-Row-Functions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C

• Question 174:

  Examine the description of the PRODUCT_STATUS table:

  

  The status column contains the values 'in stock' or 'out of stock' for each row.

  Which two queries will execute successfully?

  A. SELECT prod_id Il q" 's not available" FROM product_status where status = 'OUT OF STOCK';
  B. SELECT prod_id II q'l's not available)' FROM product_status WHERE status = 'OUT OF STOCK';
  C. SELECT prod_id "CURRENT AVAILABILITY" II q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';
  D. SELECT prod_id I I q' ('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';
  E. SELECT prod_id I I q' ('s not available)' ' CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';
  F. SELECT prod_id q's not available" FROM product_status WHERE status = 'OUT OF STOCK';
  B. SELECT prod_id II q'l's not available)' FROM product_status WHERE status = 'OUT OF STOCK';
  D. SELECT prod_id I I q' ('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

  ---

  Explanation/Reference:

• Question 175:

  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 176:

  Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED RETENTION? (Choose two.)

  A. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.
  B. UNDO_RETENTION specifies how long all types of UNDO are retained.
  C. Unexpired UNDO is always retained.
  D. Active UNDO is always retained.
  E. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
  C. Unexpired UNDO is always retained.
  D. Active UNDO is always retained.

  ---

  Explanation/Reference:

  By definition, undo extents that still have to overlive the retention period are unexpired. So, when undo_retention is set and retention is guaranteed, unexpired undo is always retained.

  As opposed to the case in which retention is noguarantee, where also unexpired extents can be overwritten IF and when needed.

  In this particolar case, so, I would say C-D are the correct answers.