Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 41:

  What is true about non-equijoin statement performance? (Choose two.)

  A. Table aliases can improve performance

  B. The BETWEEN condition always performs better than using the >= and <= conditions

  C. The join syntax used makes no difference to performance

  D. The BETWEEN condition always performs less well than using the >= and <= conditions

  E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax

  Correct Answer: AC

  There's no performance benefit or hit by using ANSI joins rather than traditional joins, but by using ANSI joins, your queries are more portable between DBMS platforms, and they're a bit easier to read.

  http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm

• Question 42:

  Which two statements are true about the DUAL table? (Choose two.)

  A. It can be accessed only by the SYS user

  B. It consists of a single row and single column of VARCHAR2 data type

  C. It can display multiple rows but only a single column

  D. It can be used to display only constants or pseudo columns

  E. It can be accessed by any user who has the SELECT privilege in any schema

  F. It can display multiple rows and columns

  Correct Answer: BF

  SQL> SELECT level, sysdate 2 FROM dual 3 CONNECT BY 4 level <= 4;

  LEVEL SYSDATE

  1 06-AUG-20 2 06-AUG-20 3 06-AUG-20 4 06-AUG-20

  It can return multple rows and columns.

  D is wrong, you just create a brand new user and do not grant any privilege but still it can select dual table.

  Reference: https://en.wikipedia.org/wiki/DUAL_table

• Question 43:

  Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

  A. A table can have only one primary key but multiple foreign keys

  B. A table can have only one primary key and one foreign key

  C. The foreign key columns and parent table primary key columns must have the same names

  D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted

  E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

  F. Only the primary key can be defined at the column and table level

  G. Primary key and foreign key constraints can be defined at both the column and table level

  Correct Answer: ADEG

  A. A table can have only one primary key but multiple foreign keys --> For sure

  B. A table can have only one primary key and one foreign key --> There is no such restrictions

  C. The foreign key columns and parent table primary key columns must have the same names --> There is no such restriction.

  D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted --> Can be achieved by disabling the FK constraint and re enabling with no validate clause after performing delete on parent table.

  E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted --> CASCADE option

  F. Only the primary key can be defined at the column and table level --> No such restrictions

  G. Primary key and foreign key constraints can be defined at both the column and table level --> True.

• Question 44:

  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

  Correct Answer: AC

  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.

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

• Question 45:

  You need to calculate the number of days from 1st January 2019 until today.

  Dates are stored in the default format of DD-MON-RR.

  Which two queries give the required output? (Choose two.)

  A. SELECT TO_CHAR(SYSDATE, `DD-MON-YYYY') ?'01-JAN-2019' FROM DUAL;

  B. SELECT ROUND(SYSDATE ?'01-JAN-2019') FROM DUAL;

  C. SELECT ROUND(SYSDATE ?TO_DATE(`01/JANUARY/2019')) FROM DUAL;

  D. SELECT TO_DATE(SYSDATE, `DD/MONTH/YYYY') ?`01/JANUARY/2019' FROM DUAL;

  E. SELECT SYSDATE ?TO_DATE('01-JANUARY-2019') FROM DUAL;

  Correct Answer: CE

• Question 46:

  Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

  A. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

  B. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

  C. GLOBAL TEMPORARY TABLE space allocation occurs at session start.

  D. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

  E. A GLOBAL TEMPORARY TABLE'S definition is available to multiple sessions.

  F. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

  Correct Answer: ADE

• Question 47:

  In one of your databases, user KING is:

  1.

  Not a DBA user

  2.

  An operating system (OS) user

  Examine this command and its output:

  

  What must you do so that KING is authenticated by the OS when connecting to the database instance?

  A. Set OS_AUTHENT_PREFIX to OPS$

  B. Have the OS administrator add KING to the OSDBA group

  C. Grant DBA to KING

  D. Unset REMOTE_LOGIN_PASSWORDFILE

  E. Alter user KING to be IDENTIFIED EXTERNALLY

  Correct Answer: E

• Question 48:

  View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

  You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a

  promotion time interval.

  Examine this SQL statement:

  

  Exhibit 1.

  

  Exhibit 2.

  

  What will be the result?

  A. It gives an error because the ALL keyword is not valid

  B. It gives an error because the GROUP BY clause is not valid

  C. It executes successfully but does not give the required result

  D. It executes successfully and gives the required result

  Correct Answer: C

  (promo_end_date - promo_begin_date) is returning the number of days which the promotion was running, but no interval, which is requested by the question. so logically the query is not generating desired output.

• Question 49:

  Examine the description of the PROMOTIONS table:

  

  You want to display the unique promotion costs in each promotion category.

  Which two queries can be used? (Choose two.)

  A. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;

  B. SELECT DISTINCT promo_cost || ' in ` || DISTINCT promo_category FROM promotions ORDER BY 1;

  C. SELECT DISTINCT promo_category || ` has ` || promo_cost AS COSTS FROM promotions ORDER BY 1;

  D. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

  E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

  Correct Answer: CE

• Question 50:

  In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:

  1.

  A size of 100 MB

  2.

  AUTOEXTEND is off

  3.

  UNDO_RETENTION is set to 15 minutes

  4.

  It has RETENTION GUARANTEE

  UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.

  What will happen when the next update is attempted by any transaction?

  A. It succeeds and the generated undo is stored in SYSTEM.

  B. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".

  C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

  D. It succeeds and the generated undo is stored in SYSAUX.

  E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

  Correct Answer: B

  To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.