1Z0-082 practice questions 141–150

1. Question 141 · 167 in bank

   A script abc.sql must be executed to perform a job. A database user HR, who is defined in this database, executes this command: $ sqlplus hr/hr@orcl @abc.sql What will happen upon execution?

   • AThe command succeeds and HR will be connected to the orcl and abc.sql databases
   • BThe command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed
   • CThe command fails because the script must refer to the full path name
   • DThe command fails and reports an error because @ is used twice

   Show answer

   Answer: B

   Explanation

   The correct answer is B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed. The sqlplus command is used to connect to an Oracle database and execute SQL statements. The @ symbol is used to specify a script file to be executed. In the command $ sqlplus hr/hr@orcl @abc.sql, the user HR is connecting to the orcl database instance and specifying the abc.sql script to be executed. The command will succeed and HR will be connected to the orcl database instance and the abc.sql script will be executed.

   Source: Adapted from community discussion — Lalala8

   Next question

2. Question 142 · 167 in bank

   Select 3

   Which three statements are true regarding indexes? (Choose three.)

   • AA UNIQUE index can be altered to be non-unique
   • BA SELECT statement can access one or more indices without accessing any tables
   • CA table belonging to one user can have an index that belongs to a different user
   • DAn update to a table can result in updates to any or all of the table's indexes
   • EWhen a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
   • FAn update to a table can result in no updates to any of the table's indexes

   Show answer

   Answer: BDF

   Explanation

   I would also suggest: BCD Especially for D Oracle says following: The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes. Source: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895 But F seems also like it's true, just like you mentioned if someone updates only non indexed columns in the table, there shouldn't be any changes done to the index. I am confused as to what the right answers are for sure - is it possible that out of the 6 choices 4 are right, but you only have to choose 3 of them?

   Source: Adapted from community discussion — adoptc94

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

3. Question 143 · 167 in bank

   Select 3

   Which three statements are true about Oracle synonyms? (Choose three.)

   • AA synonym cannot be created for a PL/SQL package
   • BA synonym can be available to all users
   • CA SEQUENCE can have a synonym
   • DA synonym created by one user can refer to an object belonging to another user
   • EAny user can drop a PUBLIC synonym

   Show answer

   Answer: BCD

   Explanation

   Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

   Source: Adapted from community discussion — Mandar79

   References

   • Oracle documentation
   • Oracle documentation

   Next question

4. Question 144 · 167 in bank

   Select 2

   View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks require subqueries? (Choose two.)

   

   • ADisplay the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE
   • BDisplay suppliers whose PROD_LIST_PRICE is less than 1000
   • CDisplay products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
   • DDisplay the total number of products supplied by supplier 102 which have a product status of obsolete
   • EDisplay the minimum PROD_LIST_PRICE for each product status

   Show answer

   Answer: AC

   Next question

5. Question 145 · 167 in bank

   Select 3

   Which three statements are true concerning logical and physical database structures? (Choose three.)

   • AAll tablespaces may have one or more data files
   • BThe extents of a segment must always reside in the same datafile
   • CA smallfile tablespace might be bigger than a bigfile tablespace
   • DA segment can span multiple data files in some tablespaces
   • EA segment's blocks can be of different sizes
   • FA segment might have only one extent
   • GSegments can span multiple tablespaces

   Show answer

   Answer: CDF

   Explanation

   A is wrong. Because bigfile tablespaces may have only one datafile B is wrong. Because if a tablespace has more than one datafile then the extents of the segment can be in any one or all of the data files. Remember that tablespaces are LOGICAL objects and the segment lives in the logical object, regardless of which file the extent exists on. C is correct. Smallfile tablespaces may have many datafiles that collectively exceed the the size of the datafile that is in the bigfile tablespace D is correct. The opposite of option B. A tablespace is a logical object made up of one or more datafiles. With locally managed tablespaces the server will decide where the next extent allocation should be done for a segment E is wrong. A segment must have all it's extents in the same tablespace, and block size is defined at tablespace level F is correct. If a segment has row pieces that collectively are less than or equal to the total storage requirement of one extent then the segment will have only one extent allocated to it G is wrong. A segment can reside in one tablespace only

   Source: Adapted from community discussion — Franky_T

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

6. Question 146 · 167 in bank

   Select 3

   The SALES_Q1 and USERS tablespaces exist in one of your databases and TEMP is a temporary tablespace. Segment creation is not deferred. You execute this command:

   

   Which three statements must be true so that the SALES user can create tables in SALES_Q1? (Choose three.)

   • AThe sales user must have a quota on the TEMP tablespace
   • BThe sales user must have a quota on the SALES_Q1 tablespace to hold the initial extends of all tables they plan to create in their schema
   • CThe sales user must have been granted the CREATE SESSION privilege
   • DThe sales user must have their quota on the users tablespace removed
   • EThe sales user must have a quota on the SALES_Q1 tablespace to hold all the rows to be inserted into any table in their schema
   • FThe sales user must have been granted the CREATE TABLE privilege

   Show answer

   Answer: BCF

   Explanation

   Why would D be right? The database checks the users quota in the tablespace that he wants to create the object in. Having quota in another tablespace isn't any issue so why would you need to remove the quota from tablespace users to be able to create objects in tablespace sales_q1? The right answers are: B -> The user need quota in the tablespace he wants to create the object (sales_q1) C -> User needs to be able to create a session (login in) to the database to create objects in the first place F -> User needs to have the right privilege to create the object (in this case a table) D is complete nonsense given the question and won't help in any way to create objects in sales_q1 tablespace

   Source: Adapted from community discussion — adoptc94

   References

   • Oracle documentation

   Next question

7. Question 147 · 167 in bank

   Select 3

   Which three statements are true about table data storage in an Oracle Database? (Choose three.)

   • AData block headers contain their own Data Block Address (DBA)
   • BA table row piece can be chained across several database blocks
   • CMultiple row pieces from the same row may be stored in different database blocks
   • DMultiple row pieces from the same row may be stored in the same block
   • EData block free space is always contiguous in the middle of the block
   • FIndex block free space is always contiguous in the middle of the block

   Show answer

   Answer: ACD

   Explanation

   A. Data block headers contain their own Data Block Address (DBA) - True B. A table row piece can be chained across several database blocks - Wrong, a row may be stored in multiple row pieces which are then chained across several database blocks. The row is chained not the row piece. C. Multiple row pieces from the same row may be stored in different database blocks - True D. Multiple row pieces from the same row may be stored in the same block - True E. Data block free space is always contiguous in the middle of the block - Wrong F. Index block free space is always contiguous in the middle of the block - Wrong "As the database fills a data block from the bottom up, the amount of free space between the row data and the block header decreases." No mention of middle of the block anywhere in the Oracle Docs. https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/tables-and-table-clusters.html#GUID-37546C88-24EE-47BE-8662-A9CED99BB90F

   Source: Adapted from community discussion — musafir

   References

   • Oracle documentation
   • Oracle documentation

   Next question

8. Question 148 · 167 in bank

   Select 2

   Examine the description of the BOOKS table:

   

   The table has 100 rows. Examine this sequence of statements issued in a new session: INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.)

   • AThe second ROLLBACK command does nothing
   • BThe second ROLLBACK command replays the delete
   • CThe first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
   • DThe second ROLLBACK command undoes the insert
   • EThe first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row

   Show answer

   Answer: CD

   Explanation

   The correct answer is C,D I Tested it in my local database 19.11.0.0.0 COUNT(*) ---------- 10 1 row selected. 1 row created. Savepoint created. 11 rows deleted. Rollback complete. COUNT(*) ---------- 11 1 row selected. Rollback complete. COUNT(*) ---------- 10 1 row selected.

   Source: Adapted from community discussion — ErickZc

   Next question

9. Question 149 · 167 in bank

   Select 3

   Which three statements are true about external tables in Oracle 18c and later releases? (Choose three.)

   • AExternal table files can be used for other external tables in a different database
   • BThe ORACLE_LOADER access driver can be used to unload data from a database into an external table
   • CThe ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table
   • DThey cannot be partitioned
   • EThe ORACLE_DATAPUMP access driver can be used to load data into a database from an external table
   • FThey support UPDATEs but not INSERTs and DELETEs

   Show answer

   Answer: ACE

   Explanation

   ACE - Correct https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm B - Incorrect The ORACLE_LOADER access driver is the default. It can perform only data loads, and the data must come from text datafiles C and E - Correct The ORACLE_DATAPUMP access driver can perform both loads and unloads. F - Incorrect https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-697B86CC-875C-4F68-AF80-49B41F33AE45 You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables. D - Incorrect Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise. https://oracle-base.com/articles/12c/partitioned-external-tables-12cr2#:~:text=Partitioned%20external%20tables%20were%20introduced,are%20supported%20with%20some%20restrictions.

   Source: Adapted from community discussion — Mandar79

   References

   • Oracle documentation
   • Oracle documentation

   Next question

10. Question 150 · 167 in bank

    Select 3

    Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

    • AThe Oracle join syntax supports creation of a Cartesian product of two tables
    • BThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
    • CThe SQL:1999 compliant ANSI join syntax supports natural joins
    • DThe SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
    • EThe Oracle join syntax only supports right outer joins
    • FThe Oracle join syntax supports natural joins
    • GThe Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

    Show answer

    Answer: ACD

    Next question

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

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