Free Practice Questions for Oracle 1Z0-082 Exam

Question 1

An Oracle database session has an uncommitted transaction in progress which updated 5000 rows in a table.

Which three situations does the transaction complete thereby committing the updates?

Awhen a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

Bwhen a CREATE INDEX statement is executed successfully in the same session

Cwhen a COMMIT statement is issued by the same user from another session in the same database instance

Dwhen the session logs out successfully

Ewhen a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

Fwhen a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Correct Answer: A. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT; B. when a CREATE INDEX statement is executed successfully in the same session; D. when the session logs out successfully

Question 2

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?

ASELECT prod_id Il q''s not available' FROM product_status where status = 'OUT OF STOCK';

BSELECT prod_id II q'l's not available)' FROM product_status WHERE status = 'OUT OF STOCK';

CSELECT prod_id 'CURRENT AVAILABILITY' II q'<'s not available)' FROM product_status WHERE status = 'OUT OF STOCK

DSELECT prod_id I I q' (*s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK

DSELECT prod_id I I q' ('s not available) * 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK

ESELECT prod_id q's not available' FROM product_status WHERE status = 'OUT OF STOCK*;

Correct Answer: B, D, D

Question 3

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

AConfigure a nonstandard block size for a new database

BRegister a new database with an available Enterprise Manager Management server

CChange the standard block size of an existing database

DConfigure incremental backups for a new database

EEnable flashback database for an existing database

Correct Answer: A. Configure a nonstandard block size for a new database; B. Register a new database with an available Enterprise Manager Management server

Question 4

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

AAggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query

BWHERE and HAVING clauses can be used in the same statement only if applied to different table columns

CThe HAVING clause can be used with aggregating functions in subqueries

DThe WHERE clause can be used to exclude rows before dividing them into groups

EThe WHERE clause can be used to exclude rows after dividing them into groups

Correct Answer: C. The HAVING clause can be used with aggregating functions in subqueries; D. The WHERE clause can be used to exclude rows before dividing them into groups

Question 5

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.

Correct Answer: B. It fails and returns the error message ''ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'''.

Explanation:

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

Question 6

You start your database instance in NOMOUNT state. Which two actions are performed?

AThe control files are opened.

BThe consistency of the database is checked.

CAll required background processes are started.

DSYS can access the database.

EMemory is allocated for the SG1

Correct Answer: C. All required background processes are started.; E. Memory is allocated for the SG1

Question 7

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

Correct Answer: C. CURRENT_DATE returns the current date and time as per the session time zone; F. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE

Explanation:

https://www.w3resource.com/oracle/datetime-functions/oracle-current_timestamp-function.php

Question 8

You want to use table compression suitable for OLTP that will:

Compress rows for all DML statements on that table

Minimize the overheads associated with compression

Which compression option is best suited for this?

ACOLUMN STORE COMPRESS FOR QUERY LOW

BROW STORE COMPRESS BASIC

CCOLUMN STORE COMPRESS FOR ARCHIVE LOW

DCOLUMN STORE COMPRESS FOR ARCHIVE HIGH

EROW STORE COMPRESS ADVANCED

Correct Answer: E. ROW STORE COMPRESS ADVANCED

Question 9

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

AA table can have only one primary key but multiple foreign keys

BA table can have only one primary key and one foreign key

CThe foreign key columns and parent table primary key columns must have the same names

DIt is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted

EIt 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

FOnly the primary key can be defined at the column and table level

GPrimary key and foreign key constraints can be defined at both the column and table level

Correct Answer: A. A table can have only one primary key but multiple foreign keys; C. The foreign key columns and parent table primary key columns must have the same names; 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; G. Primary key and foreign key constraints can be defined at both the column and table level

Question 10

Which two statements are true about segment types in an Oracle Database?

ATable segments always have two or more extents.

BTemporary segments are only stored in a temporary tablespace.

CUndo segments are only stored in an undo tablespace.

DCluster segments may contain data from multiple tables.

EIndex segments always have two or more extents.

Correct Answer: B. Temporary segments are only stored in a temporary tablespace.