Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 101:

  In which three situations does a new transaction always start? (Choose three.)

  A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

  B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session

  C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

  D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session

  E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session

  F. when issuing a DML statement after a DML statement failed in the same session

  Correct Answer: ADF

  https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-7690181F-8293-47B9-ADC1-905BD9C3AF57

• Question 102:

  Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

  A. the first column in the first select of the compound query

  B. the first varchab2 column in the first select of the compound query

  C. the first number column in the first select of the compound query

  D. the first number or varchar2 column in the last select of the compound query

  E. the first column in the last select of the compound query

  Correct Answer: A

• Question 103:

  Your database instance was shut down normally and then started in NOMOUNT state. You then execute this command:

  ALTER DATABASE MOUNT;

  Which two actions are performed? (Choose two.)

  A. The online redo logs are opened

  B. The online data files are opened

  C. The alert log records the execution details

  D. The Oracle background processes are started

  E. The initialization parameter file is read

  F. The control file is read

  Correct Answer: CF

  http://facedba.blogspot.com/2014/07/oracle-database-startup-stages-and.html

• Question 104:

  Which three statements are true about Oracle synonyms? (Choose three.)

  A. A synonym cannot be created for a PL/SQL package

  B. A synonym can be available to all users

  C. A SEQUENCE can have a synonym

  D. A synonym created by one user can refer to an object belonging to another user

  E. Any user can drop a PUBLIC synonym

  Correct Answer: BCD

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

• Question 105:

  Examine the description of the SALES table: The SALES table has 55,000 rows. Examine this statement:

  

  

  Which two statements are true? (Choose two.)

  A. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.

  B. SALES1 has primary KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.

  C. SALES1 is created with 55,000 rows.

  D. SALES1 is created with no rows.

  E. SALES1 is created with 1 row.

  Correct Answer: AC

• Question 106:

  Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)

  A. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds

  B. SYSDATE can be queried only from the DUAL table

  C. CURRENT_DATE returns the current date and time as per the session time zone

  D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR

  E. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server

  F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE

  Correct Answer: CF

  https://www.w3resource.com/oracle/datetime-functions/oracle-current_timestamp-function.php

• Question 107:

  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;

  Correct Answer: A

• Question 108:

  Your database instance is started with an SPFILE.

  A PFILE is also available.

  You execute this command:

  ALTER SYSTEM SET DB_CACHE_SIZE=100K;

  Where is the value changed?

  A. in the SPFILE, SPFILE, and memory

  B. in the SPFILE and in memory

  C. only in the SPFILE

  D. in the SPFILE and PFILE

  E. only in memory

  Correct Answer: B

• Question 109:

  Which two statements are true about the SET VERIFY ON command? (Choose two.)

  A. It can be used only in SQL*Plus

  B. It displays values for variables used only in the WHERE clause of a query

  C. It can be used in SQL Developer and SQL*Plus

  D. It displays values for variables created by the DEFINE command

  E. It displays values for variables prefixed with andand

  Correct Answer: CE

• Question 110:

  Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

  A. Private Temporary Tables (PTTS) store metadata in memory only

  B. An index created with the UNUSABLE attribute has no segment

  C. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement

  D. An index that is altered to be UNUSABLE will retain its segment

  E. A table that is truncated will always have its segment removed

  Correct Answer: AB

  A - https://oracle-base.com/articles/18c/private-temporary-tables-18c Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. B - https://docs.oracle.com/html/E25494_01/indexes003.htm#CIHFIGDG Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index. C - not always, depends on the setup of the table creation