1Z0-082 practice questions 91–100

1. Question 91 · 167 in bank

   Select 3

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

   • AOracle Managed Files (OMF)
   • BOnline table segment shrink
   • COnline index segment shrink
   • DAutomatic data file extension (AUTOEXTEND)
   • ECapacity planning growth reports based on historical data in the Automatic Workload Repository (AWR)

   Show answer

   Answer: ADE

   Explanation

   A. OMF implements an automatic file name and location option. This is done at file level and has nothing to do with how the tablespace is "managed". B is wrong. Segment shrink operations are only available with ASSM, which in turn requires LMTBS. C is wrong. Same feature as option B. D is correct. AUTOEXTEND is done at file level. E is correct. Reports are always available through the AWR and has nothing to do with tablespace management type.

   Source: Adapted from community discussion — Franky_T

   Next question

2. Question 92 · 167 in bank

   Select 3

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

   • AConnect Time Failover requires the use of Transparent Application Failover (TAF)
   • BSource Routing requires the use of a name server
   • CSource Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall
   • DLoad Balancing can balance the number of connections to dispatchers when using a Shared Server configuration
   • ELoad Balancing requires the use of a name server
   • FConnect Time Failover requires the connect string to have two or more listener addresses configured

   Show answer

   Answer: CDF

   Explanation

   A. Connect Time Failover requires the use of Transparent Application Failover (TAF) --> False B. Source Routing requires the use of a name server --> False, IP address are also accepted. C. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall --> True D. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration --> True E. Load Balancing requires the use of a name server --> False, IP address are also accepted. F. Connect Time Failover requires the connect string to have two or more listener addresses configured. --> True

   Source: Adapted from community discussion — auwia

   References

   • Oracle documentation
   • Oracle documentation

   Next question

3. Question 93 · 167 in bank

   Select 2

   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.)

   • AYou can no longer monitor it
   • BYou can reattach to it and monitor it
   • CIt is paused and can be resumed
   • DIt continues to run in the background
   • EIt terminates

   Show answer

   Answer: BC

   Explanation

   A. You can no longer monitor it -- Semi-wrong, can be monitored if reattached. B. You can reattach to it and monitor it -- Correct, STOP_JOB can be reattached and monitored C. It is paused and can be resumed -- Correct, As per doc it can be resumed but some tasks may have to restarted. D. It continues to run in the background -- Wrong, The job is stopped E. It terminates -- Wrong, Terminates with KILL_JOB not STOP_JOB B and C seems to be the most correct https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-export-utility.html#GUID-1A2BF459-680D-4D2B-B35F-9A3F4EE72530

   Source: Adapted from community discussion — musafir

   References

   • Oracle documentation
   • Oracle documentation

   Next question

4. Question 94 · 167 in bank

   Select 4

   In one of your databases, you create a user, HR, and then execute this command: GRANT CREATE SESSION TO hr WITH ADMIN OPTION; Which four actions can HR perform? (Choose four.)

   • ARevoke the CREATE SESSION privilege from other users
   • BRevoke the CREATE SESSION privilege from user HR
   • CLog in to the database instance
   • DGrant the CREATE SESSION privilege with ADMIN OPTION to other users
   • EExecute DDL statements in the HR schema
   • FExecute DML statements in the HR schema

   Show answer

   Answer: ABCD

   Explanation

   A. Revoke the CREATE SESSION privilege from other users - ORA-01031: insufficient privileges - false. B. Revoke the CREATE SESSION privilege from user HR – it works, true. C. Log in to the database instance – true. D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users – true. E. Execute DDL statements in the HR schema – false. F. Execute DML statements in the HR schema – it works if somebody with appropriate rights creates some objects in hr’s schema. – so, it is also true. So, BCDF.

   Source: Adapted from community discussion — M_ORACLE_RU

   Next question

5. Question 95 · 167 in bank

   Select 2

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

   • AAggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query
   • BWHERE and HAVING clauses can be used in the same statement only if applied to different table columns
   • CThe HAVING clause can be used with aggregating functions in subqueries
   • DThe WHERE clause can be used to exclude rows before dividing them into groups
   • EThe WHERE clause can be used to exclude rows after dividing them into groups

   Show answer

   Answer: CD

   Explanation

   A is wrong … look at this example : select file#,avg(rfile#) from v$datafile Group by file# having Count(TS#) >0 file# avg(rfile#) ----------------------- 1 1 6 6 5 5 8 1024 3 3 7 10

   Source: Adapted from community discussion — ama

   Next question

6. Question 96 · 167 in bank

   Select 2

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

   • AThe generation of UNDO generates REDO
   • BDML modifies Oracle database objects and only generates UNDO
   • CThe generation of REDO generates UNDO
   • DDML modifies Oracle database objects and only generates REDO
   • EDML modifies Oracle database objects and generates UNDO and REDO

   Show answer

   Answer: AE

   Explanation

   I would say A is correct - "Whenever we do insert in the database, it does following things: - Insert the row in the buffer - generate the undo in the buffer - Write the redo changes i.e insert rows in redo logs - Write the redo changes for undo segments in the redo logs. So A and E should be correct answers.

   Source: Adapted from community discussion — Rivaldo11

   References

   • Oracle documentation

   Next question

7. Question 97 · 167 in bank

   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?

   • Aas SCOTT in DALLAS_DB
   • Bas SCOTT in BOSTON_DB
   • Cas SCOTT in BOSTON_DB and SYS in DALLAS_DB
   • Das SYS in both the databases
   • Eas SCOTT in both the databases

   Show answer

   Answer: B

   Explanation

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

   Source: Adapted from community discussion — NowOrNever

   References

   • Oracle documentation
   • Oracle documentation

   Next question

8. Question 98 · 167 in bank

   Select 3

   Which three statements are true about the DESCRIBE command? (Choose three.)

   • AIt displays the PRIMARY KEY constraint for any column or columns that have that constraint
   • BIt can be used from SQL Developer
   • CIt displays the NOT NULL constraint for any columns that have that constraint
   • DIt can be used to display the structure of an existing view
   • EIt displays all constraints that are defined for each column
   • FIt can be used only from SQL*Plus

   Show answer

   Answer: BCD

   Next question

9. Question 99 · 167 in bank

   Examine the description of the CUSTOMERS table:

   

   For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used?

   • ASELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
   • BSELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level !=NULL;
   • CSELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;
   • DSELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
   • ESELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

   Show answer

   Answer: E

   Explanation

   A - FALSE; returns error 'ORA-00904: "DUE_AMMOUNT": invalid identifier'. Conditions in WHERE clause are evaluated before column names between SELECT and FROM, so expression alias DUE_AMMOUNT is not known and error is returned. B - FALSE; != is not a valid operator for handling with NULL values. No rows are returned when this operator is used. C - FALSE; <> is not a valid operator for handling with NULL values. No rows are returned when this operator is used. D - FALSE; same asnwer as B E - TRUE; IS NOT NULL is a valid operator, it is applied on both cust_income_level column and cust_credit_limit column. The other column is a source for computate DUE_AMMOUNT value. When a value of cust_credit_limit is NULL, then also DUE_AMMOUNT is null and therefore the appropriate row is not displayed.

   Source: Adapted from community discussion — nautil2

   Next question

10. Question 100 · 167 in bank

    Select 2

    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.)

    • ACONCAT (qty_sold, invoice_date) : requires explicit conversion
    • Binvoice_date = '15-march-2019' : uses implicit conversion
    • Cinvoie_date > '01-02-2019' : uses implicit conversion
    • Dqty_sold BETWEEN '101' AND '110' : uses implicit conversion
    • Eqty_sold = '0554982' uses implicit conversion

    Show answer

    Answer: BD

    Explanation

    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 ABDE - producing no errors BDE - are the correct ones

    Source: Adapted from community discussion — NowOrNever

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-082 practice, question 91. 167 questions available.