Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 91:

  Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

  A. Column names in each SELECT in the compound query can be different

  B. The number of columns in each SELECT in the compound query can be different

  C. Reversing the order of the intersected tables can sometimes affect the output

  D. INTERSECT returns rows common to both sides of the compound query

  E. INTERSECT ignores NULLs

  Correct Answer: AD

  https://www.oracletutorial.com/oracle-basics/oracle-intersect/

• Question 92:

  Examine the description of the customers table:

  

  You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit Is below 1000.

  Examine this partial query:

  SELECT cust_last_name, cust_credit_limit FROM customers

  Which two where conditions give the required result?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: AB

• Question 93:

  Which two actions can you perform using DBCA for an existing database?

  A. Create a template that can be used to clone the database.

  B. Change the server mode from dedicated to shared, and vice versa.

  C. Change the character set.

  D. Create an additional listener.

  E. Create nonstandard block size tablespaces.

  Correct Answer: BC

• Question 94:

  Which two statements are true about the PMON background process? (Choose two.)

  A. It registers database services with all local and remote listeners known to the database instance.

  B. It rolls back transactions when a process fails.

  C. It records checkpoint information in the control file.

  D. It frees unused temporary segments.

  E. It frees resources held by abnormally terminated processes.

  Correct Answer: BE

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14220/process.htm

• Question 95:

  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

  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

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

  The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE

  User U1 has a 1 MB quota in tablespace DATA.

  U1 executes this command:

  SQL> CREATE TABLE t1 AS

  (SELECT object_name, sharing, created

  FROM dba_objects);

  U1 complains that the command is taking too long to execute.

  In the alert log, the database administrator (DBA) finds this:

  2017-03-06T12:15:17.183438+05:30

  statement in resumable session `User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace `DATA'

  Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

  A. Add a data file to DATA

  B. Drop other U1 objects in DATA

  C. Increase U1's quota sufficiently in DATA

  D. Set DEFERRED_SEGMENT_CREATION to TRUE

  E. Grant UNLIMITED TABLESPACE to U1

  F. Set AUTOEXTEND ON for data files in DATA

  Correct Answer: BCE

  Set DEFERRED_SEGMENT_CREATION to TRUE, when creating the table you are already inserting rows as select, therefore the segment has already been created the D is not. Configure Set AUTOEXTEND ON for data files in DATA, if you have 1M quota it will not work.

  A. Add a data file to DATA -- Won't help as problem is quota to user not the space

  B. Drop other U1 objects in DATA -- Will help as it will reduce the used space from quota for that user

  C. Increase U1's quota sufficiently in DATA -- Will help.

  D. Set DEFERRED_SEGMENT_CREATION to TRUE -- Won't help, segments has to be created as its CTAS

  E. Grant UNLIMITED TABLESPACE to U1 -- Will help.

  F. Set AUTOEXTEND ON for data files in DATA -- Won't help as problem is quota to user not the space

• Question 97:

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

  A. There can be only one UNDO tablespace created in a database.

  B. An instance will crash if the active undo tablespace is lost.

  C. An UNDO tablespace may be owned by only one instance.

  D. UNDO segments are owned by SYSTEM.

  E. UNDO segments are owned by SYSBACKUP.

  Correct Answer: BC

• Question 98:

  Which three Oracle database space management features will work with both Dictionary and Locally managed tablespaces? (Choose three.)

  A. Oracle Managed Files (OMF)

  B. Online table segment shrink

  C. Online index segment shrink

  D. Automatic data file extension (AUTOEXTEND)

  E. Capacity planning growth reports based on historical data in the Automatic Workload Repository (AWR)

  Correct Answer: ADE

• Question 99:

  The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations.

  Each database has a tnsnames.ora file defining DALLAS_DB as a service name.

  Examine this command:

  CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING `dallas_db';

  How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

  A. as SCOTT in DALLAS_DB

  B. as SCOTT in BOSTON_DB

  C. as SCOTT in BOSTON_DB and SYS in DALLAS_DB

  D. as SYS in both the databases

  E. as SCOTT in both the databases

  Correct Answer: B

  https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12 Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access. https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

• Question 100:

  Which three statements are true about views in an Oracle database? (Choose three.)

  A. Views can be updated without the need to re-grant privileges on the view

  B. Tables in the defining query of a view must always exist in order to create the view

  C. The WITH CHECK clause prevents certain rows from being displayed when querying the view

  D. Data Manipulation Language (DML) can always be used on views

  E. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

  F. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

  G. The WITH CHECK clause prevents certain rows from being updated or inserted

  Correct Answer: AFG