1Z0-082 practice questions 101–110

1. Question 101 · 167 in bank

   Select 3

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

   • Aundo
   • Bindex
   • Cstored procedures
   • Dsequences
   • Etables
   • Fclusters

   Show answer

   Answer: ABE

   Explanation

   1.User Segments -Table, table partition, or table cluster -LOB or LOB partition -Index or index partition 2.Temporary Segments 3.Undo Segments Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-7DA83E64-9FF1-45A7-A9AC-D4997DDE0866 A B E F should be correct??

   Source: Adapted from community discussion — ogdru

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

2. Question 102 · 167 in bank

   Select 2

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

   • AColumn names in each SELECT in the compound query can be different
   • BThe number of columns in each SELECT in the compound query can be different
   • CReversing the order of the intersected tables can sometimes affect the output
   • DINTERSECT returns rows common to both sides of the compound query
   • EINTERSECT ignores NULLs

   Show answer

   Answer: AD

   Explanation

   The number has to be the same but the names can be different. In that page you linked, in fact, I see no reference to the fact that the names must be the same, even though they are in the example.

   Source: Adapted from community discussion — SimoneF

   Next question

3. Question 103 · 167 in bank

   Select 2

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

   • ACURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds
   • BSYSDATE can be queried only from the DUAL table
   • CCURRENT_DATE returns the current date and time as per the session time zone
   • DSYSDATE can be used in expressions only if the default date format is DD-MON-RR
   • ESYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server
   • FCURRENT_TIMESTAMP returns the same date as CURRENT_DATE

   Show answer

   Answer: CF

   Explanation

   I think A and C are correct. SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SQL> select sysdate,current_date,current_timestamp from dual; SYSDATE CURRENT_DATE CURRENT_TIMESTAMP -------------------- -------------------- --------------------------------------------------------------------------- 28-SEP-2021 14:32:48 28-SEP-2021 14:32:48 28-SEP-21 02.32.48.201457 PM +03:00 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions036.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm

   Source: Adapted from community discussion — eyildiz

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

4. Question 104 · 167 in bank

   The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day on the week. Which query can be used?

   • ASELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
   • BSELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
   • CSELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
   • DSELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

   Show answer

   Answer: B

   Explanation

   "The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week." 1 is Sunday in that case. Correct answer is B.

   Source: Adapted from community discussion — saif_alrwiliy

   Next question

5. Question 105 · 167 in bank

   Select 3

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

   • AAdd a data file to DATA
   • BDrop other U1 objects in DATA
   • CIncrease U1's quota sufficiently in DATA
   • DSet DEFERRED_SEGMENT_CREATION to TRUE
   • EGrant UNLIMITED TABLESPACE to U1
   • FSet AUTOEXTEND ON for data files in DATA

   Show answer

   Answer: BCE

   Explanation

   I think 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.

   Source: Adapted from community discussion — elcaso34

   Next question

6. Question 106 · 167 in bank

   Select 3

   Which three statements are true about the Oracle Data Dictionary? (Choose three.)

   • AData dictionary views are created by joins of dictionary base tables and DBA-defined tables
   • BThe data dictionary is created and maintained by the database administrator
   • CViews with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary
   • DBase tables can be queried directly
   • EIt is owned by the SYSTEM user
   • FUsernames of all users including database administrators are stored in the data dictionary

   Show answer

   Answer: CDF

   Explanation

   A is wrong. "DBA_defined tables" does not fit here. Dictionary views are based on system base tables only. B is wrong. Internally created and managed by the Oracle server. C is correct. They all have to reference the same base tables, they only display filtered rowsets from these tables. D is correct. You can query these tables, however not recommended as data is stored in a cryptic format. Consider logging in as the SYS user, since you "own" the tables you can select from them. And as DBA you can grant select on any table in any schema to any user. This is a permission (ownership) issue and not a data format issue. E is wrong. Owned by the SYS user, stored in the SYSTEM tablespace. F is correct. All user's usernames are stored in the dictionary and are queries via DBA_USERS.

   Source: Adapted from community discussion — Franky_T

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

7. Question 107 · 167 in bank

   In one of your databases, the user HR has the password HRMGR. You want to connect to a database instance whose listener listens on port 1531 by using this statement: CONNECT HR/HRMGR@orcl - No name server is used. Which statement is true about ORCL?

   • AIt must be the value of the SERVICE_NAMES parameter on the client side
   • BIt must resolve to a valid connect descriptor in the server's tnsnames.ora file
   • CIt must resolve to a valid connect descriptor in the client's tnsnames.ora file
   • DIt must be the name of the database to whose instance HR wishes to connect
   • EIt must be the name of the server running the database to whose instance HR wishes to connect

   Show answer

   Answer: C

   Explanation

   "port (optional) is the TCP port number on which the Oracle Net listener is listening. If not specified, the default port number 1521 is assumed." So you cannot use that syntax in this case, since it would try to connect on port 1521 while the listener, as per question, is configured to listen on port 1531. C is correct because "Name server" is referred to the record in a DNS but it doesn't change the fact that the tns must point to the correct server, either using an IP or direct resolution of the address via hosts file.

   Source: Adapted from community discussion — SimoneF

   References

   • Oracle documentation

   Next question

8. Question 108 · 167 in bank

   Select 2

   Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

   • ATablespace free space can be viewed in V$TABLESPACE
   • BV$TABLESPACE displays information that is contained in the controlfile about tablespaces
   • CV$TABLESPACE displays information about tablespaces contained in the data dictionary
   • DTablespace free space can be viewed in DBA_TABLESPACES
   • EA datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

   Show answer

   Answer: BE

   Explanation

   A - false; v$tablespace is a dynamic performance view and doesn't contain any information referring to space usage B - true; see Oracle documentation, Release 19, Database Reference, Part III Dynamic Performance Views, 9 Dynamic Performance (V$) Views: V$SCHEDULER_RUNNING_JOBS to V$ZONEMAP_USAGE_STATS, 9.105 V$TABLESPACE, V$TABLESPACE displays tablespace information from the control file. C - false; dynamic performance views (like e.g. V$TABLESPACE) gets its content from instance, not from a data dictionary; these views are independent of data dictionary D - false; free space in a tablespace cannot be get from the view DBA_TABLESPACES. It can be get using views DBA_SEGMENTS and DBA_DATA_FILES E - true; see https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

   Source: Adapted from community discussion — nautil2

   References

   • Oracle documentation

   Next question

9. Question 109 · 167 in bank

   Select 2

   Examine the description of the PROMOTIONS table:

   

   You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)

   • ASELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
   • BSELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
   • CSELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
   • DSELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
   • ESELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

   Show answer

   Answer: CE

   Next question

10. Question 110 · 167 in bank

    In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties: 1. A size of 100 MB 2. AUTOEXTEND is off 3. UNDO_RETENTION is set to 15 minutes 4. It has RETENTION GUARANTEE UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens. What will happen when the next update is attempted by any transaction?

    • AIt succeeds and the generated undo is stored in SYSTEM.
    • BIt fails and returns the error message ג€ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'ג€.
    • CIt succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
    • DIt succeeds and the generated undo is stored in SYSAUX.
    • EIt succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

    Show answer

    Answer: B

    Explanation

    Its a tricky question. To me answer should be C. The undo_retention parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low. Go to this link and search this: "The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention" https://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm

    Source: Adapted from community discussion — avanand

    References

    • Oracle documentation
    • Oracle documentation
    • Oracle documentation

    Next question

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

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