1z0-082: Oracle Database Administration I

Question #: 81
Topic #: 1
Which three statements are true about table data storage in an Oracle Database? (Choose three.)

A. Data block headers contain their own Data Block Address (DBA)
B. A table row piece can be chained across several database blocks
C. Multiple row pieces from the same row may be stored in different database blocks
D. Multiple row pieces from the same row may be stored in the same block
E. Data block free space is always contiguous in the middle of the block
F. Index block free space is always contiguous in the middle of the block

Selected Answer: ABC

Question #: 82
Topic #: 1
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.)

A. The second ROLLBACK command does nothing
B. The second ROLLBACK command replays the delete
C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
D. The second ROLLBACK command undoes the insert
E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row

Selected Answer: CD

Question #: 83
Topic #: 1
Which three statements are true about external tables in Oracle 18c and later releases? (Choose three.)

A. External table files can be used for other external tables in a different database
B. The ORACLE_LOADER access driver can be used to unload data from a database into an external table
C. The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table
D. They cannot be partitioned
E. The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table
F. They support UPDATEs but not INSERTs and DELETEs

Selected Answer: ACE

Question #: 84
Topic #: 1
Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

A. The Oracle join syntax supports creation of a Cartesian product of two tables
B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
C. The SQL:1999 compliant ANSI join syntax supports natural joins
D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
E. The Oracle join syntax only supports right outer joins
F. The Oracle join syntax supports natural joins
G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

Selected Answer: ACD

Question #: 85
Topic #: 1
Which two tasks can you perform using DBCA for databases? (Choose two.)

A. Configure a nonstandard block size for a new database
B. Register a new database with an available Enterprise Manager Management server
C. Change the standard block size of an existing database
D. Configure incremental backups for a new database
E. Enable flashback database for an existing database

Selected Answer: C

Question #: 86
Topic #: 1
You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), ‘MON’), ‘dd `Monday for` fmMonth rrrr’)
What is the result?

A. It executes successfully but does not return any result
B. It returns the date for the first Monday of the next month
C. It generates an error
D. It returns the date for the last Monday of the current month

Selected Answer: B

Question #: 87
Topic #: 1
Examine this command:
CREATE UNDO TABLESPACE undotbs01
DATAFILE ‘undotbs_01.dbf’

SIZE 100M –
AUTOEXTEND ON;
Which two actions must you take to ensure UNDOTBS01 is used as the default UNDO tablespace? (Choose two.)

A. Add the SEGMENT SPACE MANAGEMENT AUTO clause
B. Set UNDO_TABLESPACE to UNDOTBS01
C. Add the NOLOGGING clause
D. Make certain that the database operates in automatic undo management mode
E. Add the ONLINE clause

Selected Answer: D

Question #: 88
Topic #: 1
Which three statements are true about views in an Oracle database? (Choose three.)

A. Views can be updated without the need to re-grant privileges on the view
B. Tables in the defining query of a view must always exist in order to create the view
C. The WITH CHECK clause prevents certain rows from being displayed when querying the view
D. Data Manipulation Language (DML) can always be used on views
E. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error
F. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error
G. The WITH CHECK clause prevents certain rows from being updated or inserted

Selected Answer: AFG

Question #: 89
Topic #: 1
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

Selected Answer: D

Question #: 90
Topic #: 1
In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.
Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?

A. T1, an index segment for the primary key, a LOB segment, and a lobindex segment
B. no segments are created
C. T1 only
D. T1 and an index segment created for the primary key only
E. T1, an index segment for the primary key, and a LOB segment only

Selected Answer: B

Question #: 91
Topic #: 1
Which three activities are recorded in the database alert log? (Choose three.)

A. Data Definition Language (DDL) statements
B. non-default database parameters
C. block corruption errors
D. deadlock errors
E. session logins and logouts

Selected Answer: BCD

Question #: 92
Topic #: 1
Which two statements are true about Enterprise Manager (EM) Express?

A. You can use a single instance of EM Express to manage multiple database running on the same server.
B. EM Express uses a separate repository database to store target database metadata.
C. By default, EM express is available for a database after database creation using DBCA.
D. You can shut down a database instance using EM Express.
E. You cannot start up a database instance using EM Express.

Selected Answer: CD

Question #: 93
Topic #: 1
Examine this SQL statement:
SELECT cust_id, cust_last_name `Last Name`

FROM customers –

WHERE country_id = 10 –

UNION –
SELECT cust_id CUST_NO, cust_last_name

FROM customers –

WHERE country_id = 30 –
Identify three ORDER BY clauses, any one of which can complete the query successfully. (Choose three.)

A. ORDER BY ג€Last Nameג€
B. ORDER BY 2, 1
C. ORDER BY 2, cust_id
D. ORDER BY CUST_NO
E. ORDER BY ג€CUST_NOג€

Selected Answer: ABC

Question #: 94
Topic #: 1
Which two statements are true about the configuration and use of UNDO_RETENTION with GURANTEED RETENTION? (Choose two.)

A. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.
B. UNDO_RETENTION specifies how long all types of UNDO are retained.
C. Unexpired UNDO is always retained.
D. Active UNDO is always retained.
E. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

Selected Answer: CD

Question #: 95
Topic #: 1
Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
SELECT MAX(unit_price * quantity) `Maximum Order`
FROM order_items;
Statement 2:
SELECT MAX(unit_price * quantity) `Maximum Order`

FROM order_items –
GROUP BY order_id;
Which two statements are true?

A. Statement 1 returns only one row of output.
B. Statement 2 returns only one row of output.
C. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
D. Both the statements give the same output.
E. Statement 2 may return multiple rows of output.

Selected Answer: C

Question #: 96
Topic #: 1
Which three files are used by conventional path SQL*Loader when the TABLE option is not specified? (Choose three.)

A. dump files
B. control files
C. password files
D. bad files
E. input files

Selected Answer: BDE

Question #: 97
Topic #: 1
Which three statements are true about the naming methods and their features supported by Oracle database used to resolve connection information? (Choose three.)

A. Local Naming requires setting the TNS_ADMIN environment variable on the client side.
B. A client can connect to an Oracle database instance even if no client side network admin has been configured.
C. Directory Naming can be used if Connect-Time Failover is required.
D. Easy Connect supports TCP/IP and SSL.
E. Local naming can be used if Connect-Time Failover is required.
F. Directory Naming requires setting the TNS_ADMIN environment variable on the client side.

Selected Answer: BCE

Question #: 98
Topic #: 1
Which two statements are true about User Authentication in an Oracle Database? (Choose two.)

A. Password authentication must be used for system-privileged administrative users.
B. Password File authentication must be used for system-privileged administrative users.
C. Operating System authentication may be used for system-privileged administrative users.
D. Password File authentication is supported for any type of database user.
E. REMOTE_LOGIN_PASSWORDFILE must be set to exclusive to permit password changes for system-privileged administrative users.

Selected Answer: CD

Question #: 99
Topic #: 1
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

A. the first NUMBER column in the first SELECT of the compound query
B. the first NUMBER or VARCHAR2 column in the last SELECT of the compound query
C. the first column in the last SELECT of the compound query
D. the first column in the first SELECT of the compound query
E. the first VARCHAR2 column in the first SELECT of the compound query

Selected Answer: D

Question #: 100
Topic #: 1
Examine the description of the PRODUCT_STATUS table:

The STATUS column contains the values ‘IN STOCK’ or ‘OUT OF STOCK’ for each row.

Which two queries will execute successfully? (Choose two.)

A.
B.
C.
D.
E.
F.

Selected Answer: DF

Question #: 101
Topic #: 1
Which two statements are true about UNDO and UNDO tablespaces? (Choose two.)

A. There can be only one UNDO tablespace created in a database.
B. UNDO segments are owned by SYSTEM.
C. UNDO segments are owned by SYSBACKUP.
D. An UNDO tablespace may be owned by only one instance.
E. An instance will crash if the active undo tablespace is lost.

Selected Answer: DE

Question #: 102
Topic #: 1
You must create a tablespace of non-standard block size in a new file system and plan to use this command:

The standard block size is 8k but other non-standard block sizes will also be used.

Which two are requirements for this command to succeed? (Choose two.)

A. DB_32K_CACHE_SIZE must be less than DB_CACHE_SIZE.
B. DB_32K_CACHE_SIZE must be set to a value that can be accommodated in the SGA.
C. DB_32K_CACHE_SIZE should be set to a value greater than DB_CACHE_SIZE.
D. The /u02 file system must have at least 100g space for the datafile.
E. The operating system must use a 32k block size.
F. DB_CACHE_SIZE must be set to a size that is smaller than DB_32K_CACHE_SIZE.

Selected Answer: BD

Question #: 103
Topic #: 1
Examine the description of the PRODUCTS table:

Which query is valid?

A. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;
C. SELECT prod_id, AVG(MAX(cost)) FROM products GROUP BY prod_id;
D. SELECT prod_id, MAX(AVG(cost)) FROM products GROUP BY prod_id;

Selected Answer: B

Question #: 104
Topic #: 1
Which two statements are true about the Oracle Data Dictionary? (Choose two.)

A. All data dictionary view join base tables to dynamic performance views.
B. Data dictionary base tables can be queried directly.
C. It is owned by the SYS user.
D. It is owned by the SYSTEM user.
E. Data dictionary views are always created with queries that join two or more base tables.

Selected Answer: BC

Question #: 105
Topic #: 1
Which two Oracle database space management features require the use of locally managed tablespaces? (Choose two.)

A. Automatic data file extension (AUTOEXTEND)
B. Server-generated tablespace space alerts
C. Online segment shrink
D. Free space management with bitmaps
E. Oracle Managed Files (OMF)

Selected Answer: CD

Question #: 106
Topic #: 1
Which three statements are true about connection strings and service names used to connect to an Oracle database instance? (Choose three.)

A. A connection string must include the SID of a database instance.
B. A connection string including a service name must be defined in the tnsnames.ora file.
C. A single database instance can support connections for multiple service names.
D. A single connection string can refer to multiple database instances.
E. A service name is created by a listener.
F. Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.

Selected Answer: CDF

Question #: 107
Topic #: 1
Which three statements are true about using SQL*Plus? (Choose three.)

A. It has both command-line and graphical user interfaces (GUI).
B. It must be downloaded from the Oracle Technology Network (OTN).
C. It can run Recovery Manager (RMAN) commands.
D. It has its own commands that are separate from any SQL statements.
E. It can run scripts entered at the SQL prompt.
F. It can run scripts passed to it by a shell script.

Selected Answer: DEF

Question #: 108
Topic #: 1
18. The orders table has a column ORDER_DATE of data type DATE.

The default display format for a date is DD-MON-RR.

Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.)

A. WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE, 6), ‘MON DD YYYY’)
B. WHERE order dare > TO DATE(‘JUL 10 2018’, ‘MON DD YYYY’)
C. WHERE TO_CHAR(order date, ‘MON DD YYYY’) = ‘JAN 20 2019’
D. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘MON DD YYYY’)
E. WHERE order_date IN (TO_DATE(‘Oct 21 2018’, ‘Mon DD YYYY’), TO_CHAR(‘Nov 21 2018’, ‘Mon DD YYYY’))

Selected Answer: BC

Question #: 109
Topic #: 1
Which three statements are true about the Automatic Diagnostic Repository (ADR)? (Choose three.)

A. It can be used for problem diagnosis of a database when that database’s instance is down.
B. The ADR base is specified in the DIAGNIOSTIC_DEST database parameter.
C. It is held inside an Oracle database schema.
D. It is a file-based repository held outside any database.
E. It is only used for Oracle Database diagnostic information.

Selected Answer: ABD

Question #: 110
Topic #: 1
Which two statements are true about a self join? (Choose two.)

A. It can be an inner join.
B. It can be a left outer join.
C. It must be an equijoin.
D. The join key column must have an index.
E. It must be a full outer join.

Selected Answer: AB

Question #: 111
Topic #: 1
Which statement is true about database links?

A. Private database link creation requires the same user to exist in both the local and the remote databases.
B. A database link created in a database allows a connection from that database’s instance to the target database’s instance, but not vice versa.
C. A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.
D. A public database link can be created only by SYS.
E. A database link can be created only between two Oracle databases.

Selected Answer: B

Question #: 112
Topic #: 1
Which two queries execute successfully? (Choose two.)

A. SELECT NULLIF(NULL, 100) FROM DUAL;
B. SELECT NULLIF(100, ‘A’) FROM DUAL;
C. SELECT COALESCE(100, ‘A’) FROM DUAL;
D. SELECT COALESCE(100, NULL, 200) FROM DUAL;
E. SELECT NULLIF(100, 100) FROM DUAL;

Selected Answer: DE

Question #: 113
Topic #: 1
Which three statements are true about data block storage in an Oracle Database? (Choose three.)

A. An index block can contain row data.
B. A data block header is of a fixed length.
C. Row data is stored starting at the end of the block.
D. A table block must always contain row data.
E. A block header contains a row directory pointing to all rows in the block.

Selected Answer: ACE

Question #: 114
Topic #: 1
Which two are benefits of external tables? (Choose two.)

A. They support DELETES which transparently deletes records in the file system as if they were table rows.
B. They can be queried, transformed, and joined with other tables without having to load the data first.
C. They support UPDATES which transparently updates records in the file system as if they were table rows.
D. The results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.
E. They can be queried while the database is in the MOUNT state like dynamic performance views.

Selected Answer: BD

Question #: 115
Topic #: 1
Examine the description of the MEMBERS table:

Examine the partial query:

SELECT city, last_name LNAME FROM members …;

You want to display all cities that contain the string an. The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query? (Choose two.)

A. ORDER BY 1, 2
B. WHERE city = ‘%AN%’
C. WHERE city LIKE ‘%AN%’
D. ORDER BY last_name DESC, city ASC
E. WHERE city IN (‘%AN%’)
F. ORDER BY 1, LNAME DESC

Selected Answer: CF

Question #: 116
Topic #: 1
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A. Sequences can always have gaps.
B. Two or more tables cannot have keys generated from the same sequence.
C. A sequence’s unallocated cached values are lost if the instance shuts down.
D. A sequence can issue duplicate values.
E. A sequence can only be dropped by a DBA.
F. A sequence number that was allocated can be rolled back if a transaction fails.

Selected Answer: ACD

Question #: 117
Topic #: 1
Your database instance is started with an SPFILE.

A PFILE is also available.

You execute this command:

ALTER SYSTEM SET DB_CACHE_SIZE=100K;

Where is the value changed?

A. in the SPFILE, PFILE, and memory
B. in the SPFILE and in memory
C. only in the SPFILE
D. only in memory
E. in the SPFILE and PFILE

Selected Answer: B

Question #: 118
Topic #: 1
You want to apply the principle of Least Privilege in all your live databases.

One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.

Which two are types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package? (Choose two.)

A. analysis of privileges that a user has on other schema’s objects
B. analysis of privileges that a user has on their own schema objects
C. analysis of all privileges used by the sys user.
D. analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role
E. analysis of privileges granted directly to a role that are then used by a user who has been granted that role

Selected Answer: DE

Question #: 119
Topic #: 1
Examine the description of the SALES table:

The SALES table has 55,000 rows.

Examine this statement:

Which two statements are true? (Choose two.)

A. SALES1 is created with l row.
B. SALES1 is created with no rows.
C. SALES1 has primary KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.
E. SALES1 is created with 55,000 rows.

Selected Answer: B

Question #: 120
Topic #: 1
Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
C. Delete statements can always be done on a table through a view.
D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
E. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
F. Insert statements can always be done on a table through a view.

Selected Answer: BDE