Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 31:

  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

• Question 32:

  Which three statements are true about a self join? (Choose three.)

  A. The ON clause must be used

  B. The query must use two different aliases for the table

  C. It must be an equijoin

  D. It must be an inner join

  E. The ON clause can be used

  F. It can be an outer join

  Correct Answer: BEF

• Question 33:

  Examine this description of the TRANSACTIONS table:

  

  Which two SQL statements execute successfully? (Choose two.)

  A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;

  B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;

  C. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;

  D. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

  E. SELECT customer_id AS `CUSTOMER-ID', transaction_date AS DATE, amount + 100 `DUES AMOUNT' FROM transactions;

  Correct Answer: BD

• Question 34:

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

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

  A. The BETWEEN condition used with a non-equijoin always performs better than when using the >= and <= conditions.

  B. The BETWEEN condition used with a non-equijoin sometimes performs better than using the >= and <= conditions.

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

  D. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.

  E. The join syntax used makes no difference to performance.

  Correct Answer: E

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

• Question 36:

  You execute this command:

  CREATE SMALLFILE TABLESPACE sales DATAFILE `/u01/app/oracle/sales01.dbf SIZE 5G SEGMENT SPACE MANAGEMENT AUTO;

  Which two statements are true about the SALES tablespace? (Choose two.)

  A. It must be smaller than the smallest BIGFILE tablespace

  B. Free space is managed using freelists

  C. Any data files added to the tablespace must have a size of 5 gigabytes

  D. It uses the database default blocksize

  E. It is a locally managed tablespace

  Correct Answer: DE

  Incorrect segment management clause It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace. AUTO :Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. MANUAL :Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists

  E - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11360 Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces,

  D - Correct https://docs.oracle.com/cd/B28359_01/server.111/b28310/create005.htm#ADMIN11105 The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific.

• Question 37:

  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

  https://oracle-base.com/articles/misc/os-authentication

• Question 38:

  You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed.

  The variables used in your query are never undefined in your session.

  Which query can be used?

  A. SELECT andandcol1, andandcol2 FROM andtable WHERE andandcondition = andandcond;

  B. SELECT andcol1, andcol2 FROM andandtable WHERE andcondition;

  C. SELECT andcol1, andcol2 FROM "andtable" WHERE andcondition;

  D. SELECT `andandcol1', `andandcol2' FROM andtable WHERE `andandcondition' = `andcond';

  E. SELECT andandcol1, andandcol2 FROM andtable WHERE andandcondition;

  Correct Answer: B

• Question 39:

  Which three statements are true about advanced connection options supported by Oracle Net for connection to Oracle Database instances? (Choose three.)

  A. Connect Time Failover requires the use of Transparent Application Failover (TAF)

  B. Source Routing requires the use of a name server

  C. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall

  D. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration

  E. Load Balancing requires the use of a name server

  F. Connect Time Failover requires the connect string to have two or more listener addresses configured

  Correct Answer: CDF

  https://docs.oracle.com/en/database/oracle/oracle-database/12.2/rilin/shared-server-configuration-for-an-oracle-rac-database.html#GUID-2EFBA08D-FEEE-407F-BC42-E548DA946DF9

• Question 40:

  You have been tasked to create a table for a banking application.

  One of the columns must meet three requirements:

  Be stored in a format supporting date arithmetic without using conversion functions

  Store a loan period of up to 10 years

  Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

  A. INTERVAL YEAR TO MONTH

  B. INTERVAL DAY TO SECOND

  C. TIMESTAMP WITH LOCAL TIMEZONE

  D. TIMESTAMP

  E. TIMESTAMP WITH TIMEZONE

  Correct Answer: B

  Since the third requirement for banking application is

  'Be used for calculating interest for the number of days the loan remains unpaid'

  So, Data Type 'INTERVAL DAY TO SECOND' must be used to calculate duration in number of days.