Free OCA Practice Questions

Question 1

Which of the following correctly describes an Oracle database instance?

Accepted Answer

The System Global Area together with the background processes

Answer

The set of physical files that store data, including datafiles, control files and online redo log files

Answer

The Oracle software installed under ORACLE_HOME

Answer

The collection of schemas and objects owned by application users

Question 2

A DBA must place the database into ARCHIVELOG mode. In which state must the database be when the ALTER DATABASE ARCHIVELOG command is issued?

Accepted Answer

MOUNT, because the control file is read but the datafiles are not yet open

Answer

NOMOUNT, because the control file has not yet been read

Answer

OPEN, because the database must be fully available to users before the archiving mode can be changed

Answer

OPEN READ ONLY, so that no user transactions occur during the change

Question 3

Examine this statement:

SELECT e.last_name
FROM employees e
WHERE e.department_id NOT IN (SELECT d.department_id FROM departments d);

The DEPARTMENTS table contains one row in which DEPARTMENT_ID is null. What is the result?

Accepted Answer

No rows are returned, whatever the EMPLOYEES table contains

Answer

Every employee whose department is not listed in DEPARTMENTS is returned

Answer

An error is raised because the subquery returns a null value

Answer

Only employees whose DEPARTMENT_ID is null are returned

Question 4

An instance was started using a text initialization parameter file (PFILE). The DBA then executes:

ALTER SYSTEM SET open_cursors=500 SCOPE=BOTH;

What is the result?

Accepted Answer

The statement fails and the parameter is unchanged

Answer

It takes effect immediately and persists after the next restart

Answer

It takes effect immediately but is lost when the instance restarts

Answer

It is recorded for the next restart only

Question 5

Examine this query:

SELECT e.employee_id, d.department_id, d.department_name
FROM employees e JOIN departments d USING (department_id);

What is the result?

Accepted Answer

It fails, because a column named in a USING clause cannot be qualified

Answer

It executes and returns the employee ID, department ID and department name

Answer

It executes but produces a Cartesian product

Answer

It fails, because table aliases may not be used with a USING clause

Question 6

An instance terminates unexpectedly and is then restarted. Which statement describes the recovery Oracle performs automatically?

Accepted Answer

Redo is applied to roll the database forward, then undo reverses uncommitted transactions

Answer

Undo is applied to reverse uncommitted transactions, then redo reapplies committed ones

Answer

Only redo is applied, because uncommitted changes are never written to the datafiles

Answer

Only undo is applied, because redo is required only for media recovery

Question 7

The current year is 2026. Examine this statement:

SELECT TO_DATE('15-MAR-95','DD-MON-RR') FROM dual;

In which year does the returned date fall?

Accepted Answer

1995

Answer

2095

Answer

2026

Answer

The statement fails, because RR is not a valid format element

Question 8

User SCOTT grants the SELECT privilege on his EMPLOYEES table to user ANNA using WITH GRANT OPTION. ANNA then grants SELECT on that same table to user BEN. SCOTT now revokes SELECT from ANNA. What happens to BEN's privilege?

Accepted Answer

BEN loses the privilege automatically as well

Answer

BEN keeps the privilege, because revoking a privilege never cascades to other users

Answer

BEN keeps it until ANNA revokes it

Answer

The revoke fails, because ANNA has already granted the privilege onward

Question 9

Examine this query:

SELECT last_name, salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;

Which statement best describes the rows returned?

Accepted Answer

Five arbitrary rows, then sorted by salary

Answer

The five highest-paid employees in the table, listed in descending salary order

Answer

The five lowest-paid employees

Answer

No rows, because ROWNUM cannot be used together with ORDER BY

Question 10

Examine this query:

SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department_id;

What is the result?

Accepted Answer

An error, because a group function cannot appear in WHERE

Answer

Departments having more than five employees are returned

Answer

All departments are returned and the WHERE clause is ignored

Answer

An error, because DEPARTMENT_ID must be enclosed in a group function

Free OCA Practice Questions

Domain 1: Understanding Oracle Database Architecture

Question 1

Domain 2: Managing Users, Roles and Privileges

Question 2

Question 3

Domain 3: Moving Data

Question 4

Domain 9: Managing Undo

Question 5

Domain 11: Displaying Data from Multiple Tables Using Joins

Question 6

Domain 13: Managing Data in Different Time Zones

Question 7

Domain 14: Using Single-Row Functions to Customize Output

Question 8

Domain 15: Using Subqueries to Solve Queries

Question 9

Domain 16: Managing Sequences, Synonyms, Indexes

Question 10

The rest of the OCA blueprint

That's 10 of 1,030