Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 61:

  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

  Correct Answer: DE

  A is false cause CONCAT doesn't require explicit conversion.

  B is false cause it gives to me error ORA-00933 is not the correct format (should be 15-MAR-2019)

  C is false cause the month is not valid

• Question 62:

  Which three are types of segments in an Oracle Database? (Choose three.)

  A. undo

  B. index

  C. stored procedures

  D. sequences

  E. tables

  F. clusters

  Correct Answer: BEF

  Reference: http://www.adp-gmbh.ch/ora/concepts/segments.html

• Question 63:

  Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  A. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query

  B. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns

  C. The HAVING clause can be used with aggregating functions in subqueries

  D. The WHERE clause can be used to exclude rows before dividing them into groups

  E. The WHERE clause can be used to exclude rows after dividing them into groups

  Correct Answer: CD

• Question 64:

  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

  Correct Answer: AE

  … 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 65:

  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

  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/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12085 https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

• Question 66:

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

  You execute this command:

  

  During the export operation, you detach from the job by using CTRL+C and then execute this command:

  Export> STOP_JOB=immediate Are you sure you wish to stop the job ([yes]/no): yes Which two statements are true about the job? (Choose two.)

  A. You can no longer monitor it

  B. You can reattach to it and monitor it

  C. It is paused and can be resumed

  D. It continues to run in the background

  E. It terminates

  Correct Answer: BC

  see the example which is done on 12c version

  B. You can reattach to it and monitor it

  C. It is paused and can be resumed

• Question 68:

  In one of your databases, you create a user, HR, and then execute this command: GRANT CREATE SESSION TO hr WITH ADMIN OPTION;

  Which three actions can HR perform? (Choose three.)

  A. Revoke the CREATE SESSION privilege from other users

  B. Revoke the CREATE SESSION privilege from user HR

  C. Log in to the database instance

  D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users

  E. Execute DDL statements in the HR schema

  F. Execute DML statements in the HR schema

  Correct Answer: BCD

  SYS@linuxdb1 > create user hr identified by hr1;

  User created.

  SYS@linuxdb1 > grant create session to hr with admin option;

  Grant succeeded.

  SYS@linuxdb1 > connect hr

  Enter password:

  Connected.

  HR@linuxdb1 > show user;

  USER is "HR"

  HR@linuxdb1 > revoke create session from hr;

  Revoke succeeded.

  HR@linuxdb1 >

  (here I connected as SYS to do something else and tried to go back as HR, it didn't work).

  SYS@linuxdb1 > connect hr

  Enter password:

  ERROR:

  ORA-01045: user HR lacks CREATE SESSION privilege; logon denied

  Warning: You are no longer connected to ORACLE.

  @ > connect sys as sysdba

  Enter password:

  Connected.

  SYS@linuxdb1 > grant create session to hr;

  Grant succeeded.

  SYS@linuxdb1 > connect hr

  Enter password:

  Connected.

  HR@linuxdb1 >

  HR@linuxdb1 > revoke create session from toad;

  revoke create session from toad

  *

  ERROR at line 1:

  ORA-01031: insufficient privileges

  A is incorrect, HR can't revoke privs from another user. B is correct, it can revoke its own privs.

  Reference: https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224

• Question 69:

  Examine the description of the SALES1 table:

  

  SALES2 is a table with the same description as SALES1.

  Some sales data is duplicated in both tables.

  You want to display the rows from the SALES1 table which are not present in the SALES2 table.

  Which set operator generates the required output?

  A. INTERSECT

  B. UNION ALL

  C. UNION

  D. SUBTRACT

  E. MINUS

  Correct Answer: E

• Question 70:

  Your database instance is started with a PFILE. Examine these parameters:

  

  You want to increase the size of the buffer cache.

  Free memory is available to increase the size of the buffer cache.

  You execute the command:

  SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1024M;

  What is the outcome?

  A. The value is changed only in the PFILE and takes effect at the next instance startup

  B. The value is changed for the current instance and in the PFILE

  C. It fails because the SCOPE clause is missing

  D. Change is applied to the current instance, but does not persist after instance restart

  Correct Answer: D

  The change is applied in memory only, the change does not persist as the database can't write to the pfile

  The default SCOPE option, when you start the instance using a PFILE, is MEMORY (as well as the only scope option you can use with a pfile). If scope is omitted the default scope option is used instead. The SCOPE clause is optional and not mandatory when changing the value of a parameter!

  A is wrong, as the pfile is a read only file for the database so it can't write to it. Changes to the pfile have to be done manually by changing it directly using f.e. vi on unix systems.

  B is wrong, same reason as for why answer A is wrong

  C is wrong, as mentioned above, when omitted the scope clause defaults to MEMORY when using a pfile.

  Not related to that question but the more you know: When starting the database using a spfile you have three options for the scope (MEMORY, SPFILE and BOTH). Default value is BOTH. Reference: https://docs.oracle.com/database/121/SQLRF/statements_2017.htm#SQLRF00902