Oracle 1Z0 082 Practice Test

Question 1

Which three files are used by conventional path SQL*Loader when the TABLE option is not specified?
(Choose three.)

A. INTERVAL YEAR TO MONTH
A. dump files
B. INTERVAL DAY TO SECOND
B. control files
C. TIMESTAMP WITH LOCAL TIMEZONE
C. password files
D. TIMESTAMP
D. bad files
E. TIMESTAMP WITH TIMEZONE
E. input files

Answer:

B,D,E

User Votes:

A 1 votes

50%

A 2 votes

50%

B

50%

B 1 votes

50%

C 1 votes

50%

C 1 votes

50%

D

50%

D 2 votes

50%

E

50%

E 2 votes

50%

Question 2

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. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables
A. Statement 1 returns only one row of output.
B. The data dictionary is created and maintained by the database administrator
B. Statement 2 returns only one row of output.
C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary
C. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
D. Base tables can be queried directly
D. Both the statements give the same output.
E. It is owned by the SYSTEM user
E. Statement 2 may return multiple rows of output.
F. Usernames of all users including database administrators are stored in the data dictionary

Answer:

A,E

Explanation:
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj27781.html
The Expression can contain multiple column references or expressions, but it cannot contain another
aggregate or subquery. It must evaluate to a built-in data type. You can therefore call methods that
evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int
evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C 1 votes

50%

D

50%

D

50%

E

50%

E 1 votes

50%

F

50%

Question 3

Which two statements are true about the configuration and use of UNDO_RETENTION with
GURANTEED RETENTION? (Choose two.)

A. A synonym cannot be created for a PL/SQL package
A. UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unexpired UNDO.
B. A synonym can be available to all users
B. UNDO_RETENTION specifies how long all types of UNDO are retained.
C. A SEQUENCE can have a synonym
C. Unexpired UNDO is always retained.
D. A synonym created by one user can refer to an object belonging to another user
D. Active UNDO is always retained.
E. Any user can drop a PUBLIC synonym
E. UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.

Answer:

A,C

User Votes:

A

50%

A

50%

B

50%

B 1 votes

50%

C

50%

C

50%

D

50%

D 1 votes

50%

E

50%

E 1 votes

50%

Question 4

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. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS
A. ORDER BY “Last Name”
B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
B. ORDER BY 2, 1
C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO
C. ORDER BY 2, cust_id
D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
D. ORDER BY CUST_NO
E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers
E. ORDER BY “CUST_NO”

Answer:

BCD

User Votes:

A

50%

A 1 votes

50%

B

50%

B 2 votes

50%

C

50%

C 2 votes

50%

D

50%

D 1 votes

50%

E

50%

E

50%

Question 5

Which two statements are true about Enterprise Manager (EM) Express?

A. SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;
A. You can use a single instance of EM Express to manage multiple database running on the same server.
B. SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;
B. EM Express uses a separate repository database to store target database metadata.
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
C. By default, EM express is available for a database after database creation using DBCA.
D. SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
D. You can shut down a database instance using EM Express.
E. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
E. You cannot start up a database instance using EM Express.

Answer:

C,E

User Votes:

A 1 votes

50%

A

50%

B

50%

B

50%

C 1 votes

50%

C

50%

D

50%

D

50%

E

50%

E

50%

Question 6

Which three activities are recorded in the database alert log? (Choose three.)

A. The Oracle join syntax supports creation of a Cartesian product of two tables
A. Data Definition Language (DDL) statements
B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
B. non-default database parameters
C. The SQL:1999 compliant ANSI join syntax supports natural joins
C. block corruption errors
D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
D. deadlock errors
E. The Oracle join syntax only supports right outer joins
E. session logins and logouts
F. The Oracle join syntax supports natural joins
G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

Answer:

B,C,D

Explanation:
Reference:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/monitoring001.htm#ADMIN11247

User Votes:

A

50%

A 1 votes

50%

B

50%

B

50%

C

50%

C 1 votes

50%

D

50%

D 1 votes

50%

E

50%

E

50%

F

50%

G

50%

Question 7

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. Connect Time Failover requires the use of Transparent Application Failover (TAF)
A. T1, an index segment for the primary key, a LOB segment, and a lobindex segment
B. Source Routing requires the use of a name server
B. no segments are created
C. Source Routing enables the use of Connection Manager (CMAN) which enables network traffic to be routed through a firewall
C. T1 only
D. Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration
D. T1 and an index segment created for the primary key only
E. Load Balancing requires the use of a name server
E. T1, an index segment for the primary key, and a LOB segment only
F. Connect Time Failover requires the connect string to have two or more listener addresses configured

Answer:

B

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C 1 votes

50%

D

50%

D

50%

E

50%

E

50%

F

50%

Question 8

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. Employee 100 will have SALARY set to the same value as the SALARY of employee 200
A. It must be smaller than the smallest BIGFILE tablespace
B. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
B. Free space is managed using freelists
C. Employee 200 will have SALARY set to the same value as the SALARY of employee 100
C. Any data files added to the tablespace must have a size of 5 gigabytes
D. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
D. It uses the database default blocksize
E. Employees 100 and 200 will have the same JOB_ID as before the update command
E. It is a locally managed tablespace
F. Employees 100 and 200 will have the same SALARY as before the update command

Answer:

D,E

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C

50%

D

50%

D 1 votes

50%

E

50%

E 1 votes

50%

F

50%

Question 9

Which three statements are true about views in an Oracle database? (Choose three.)

A. Configure a nonstandard block size for a new database
A. Views can be updated without the need to re-grant privileges on the view
B. Register a new database with an available Enterprise Manager Management server
B. Tables in the defining query of a view must always exist in order to create the view
C. Change the standard block size of an existing database
C. The WITH CHECK clause prevents certain rows from being displayed when querying the view
D. Configure incremental backups for a new database
D. Data Manipulation Language (DML) can always be used on views
E. Enable flashback database for an existing database
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

Answer:

AEF

User Votes:

A

50%

A

50%

B

50%

B 1 votes

50%

C 1 votes

50%

C

50%

D

50%

D

50%

E

50%

E

50%

F 1 votes

50%

G 1 votes

50%

Question 10

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. It must be the value of the SERVICE_NAMES parameter on the client side
A. Add the SEGMENT SPACE MANAGEMENT AUTO clause
B. It must resolve to a valid connect descriptor in the server’s tnsnames.ora file
B. Set UNDO_TABLESPACE to UNDOTBS01
C. It must resolve to a valid connect descriptor in the client’s tnsnames.ora file
C. Add the NOLOGGING clause
D. It must be the name of the database to whose instance HR wishes to connect
D. Make certain that the database operates in automatic undo management mode
E. It must be the name of the server running the database to whose instance HR wishes to connect
E. Add the ONLINE clause

Answer:

B,D

User Votes:

A

50%

A 1 votes

50%

B

50%

B 1 votes

50%

C

50%

C

50%

D

50%

D

50%

E

50%

E

50%

Question 11

You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), MON), dd Monday for fmMonth rrrr)
What is the result?

A. Data block headers contain their own Data Block Address (DBA)
A. It executes successfully but does not return any result
B. A table row piece can be chained across several database blocks
B. It returns the date for the first Monday of the next month
C. Multiple row pieces from the same row may be stored in different database blocks
C. It generates an error
D. Multiple row pieces from the same row may be stored in the same block
D. It returns the date for the last Monday of the current month
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

Answer:

B

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C

50%

D

50%

D

50%

E

50%

F

50%

Question 12

Which two tasks can you perform using DBCA for databases? (Choose two.)

A. CONCAT (qty_sold, invoice_date) : requires explicit conversion
A. Configure a nonstandard block size for a new database
B. invoice_date = ’15-march-2019’ : uses implicit conversion
B. Register a new database with an available Enterprise Manager Management server
C. invoie_date > ’01-02-2019’ : uses implicit conversion
C. Change the standard block size of an existing database
D. qty_sold BETWEEN ‘101’ AND ’110’ : uses implicit conversion
D. Configure incremental backups for a new database
E. qty_sold = ‘0554982’ uses implicit conversion
E. Enable flashback database for an existing database

Answer:

AB

Explanation:
Reference:
https://docs.oracle.com/cd/B16254_01/doc/server.102/b14196/install003.htm

User Votes:

A

50%

A 1 votes

50%

B

50%

B 1 votes

50%

C

50%

C

50%

D

50%

D

50%

E

50%

E

50%

Question 13

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

A. The sales user must have a quota on the TEMP tablespace
A. The Oracle join syntax supports creation of a Cartesian product of two tables
B. The 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
B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax
C. The sales user must have been granted the CREATE SESSION privilege
C. The SQL:1999 compliant ANSI join syntax supports natural joins
D. The sales user must have their quota on the users tablespace removed
D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables
E. The 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
E. The Oracle join syntax only supports right outer joins
F. The sales user must have been granted the CREATE TABLE privilege
F. The Oracle join syntax supports natural joins
G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax

Answer:

ACD

Explanation:
https://oracle-base.com/articles/9i/ansi-iso-sql-support

User Votes:

A

50%

A 1 votes

50%

B

50%

B

50%

C

50%

C

50%

D

50%

D 1 votes

50%

E

50%

E

50%

F

50%

F

50%

G

50%

Question 14

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

A. A table can have only one primary key but multiple foreign keys
A. External table files can be used for other external tables in a different database
B. A table can have only one primary key and one foreign key
B. The ORACLE_LOADER access driver can be used to unload data from a database into an external table
C. The foreign key columns and parent table primary key columns must have the same names
C. The ORACLE_DATAPUMP access driver can be used to unload data from a database into an external table
D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
D. They cannot be partitioned
E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
E. The ORACLE_DATAPUMP access driver can be used to load data into a database from an external table
F. Only the primary key can be defined at the column and table level
F. They support UPDATEs but not INSERTs and DELETEs
G. Primary key and foreign key constraints can be defined at both the column and table level

Answer:

A,B,C

Explanation:
https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-F6948F0E-0557-4C42-9145-1897DE974CC3

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C

50%

D

50%

D

50%

E

50%

E

50%

F

50%

F

50%

G

50%

Question 15

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. It is not possible to shrink either indexes or Index Organized Tables (IOTs)
A. The second ROLLBACK command does nothing
B. It always eliminates all migrated rows if any exist in the table
B. The second ROLLBACK command replays the delete
C. To shrink a table it must have a PRIMARY KEY constraint
C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
D. To shrink a table it must have a UNIQUE KEY constraint
D. The second ROLLBACK command undoes the insert
E. To shrink a table it must have row movement enabled
E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row
F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)

Answer:

CD

User Votes:

A

50%

A

50%

B

50%

B

50%

C

50%

C

50%

D

50%

D

50%

E

50%

E

50%

F

50%

oracle 1z0-082 practice test

Oracle Database Administration I Exam

Last exam update: Nov 27 ,2024

Page 1 out of 7. Viewing questions 1-15 out of 96

Question 1

Answer:

User Votes:

Question 2

Answer:

User Votes:

Question 3

Answer:

User Votes:

Question 4

Answer:

User Votes:

Question 5

Answer:

User Votes:

Question 6

Answer:

User Votes:

Question 7

Answer:

User Votes:

Question 8

Answer:

User Votes:

Question 9

Answer:

User Votes:

Question 10

Answer:

User Votes:

Question 11

Answer:

User Votes:

Question 12

Answer:

User Votes:

Question 13

Answer:

User Votes:

Question 14

Answer:

User Votes:

Question 15

Answer:

User Votes: