oracle 1z0-082 practice test

Oracle Database Administration I Exam

Last exam update: Feb 21 ,2024
Page 1 out of 7. Viewing questions 1-15 out of 96

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
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%

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.

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

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
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
G
50%

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

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
G
0 / 1000

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
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

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
50%
E
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

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
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
G
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
G
0 / 1000

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
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

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

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%

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

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

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

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%

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

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
F
G
0 / 1000

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

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%

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

Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
F
G
0 / 1000

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%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000
To page 2