Oracle Certified Professional Practice Test

1. Which Oracle database component is responsible for managing memory allocation and caching?

System Global Area (SGA)

Program Global Area (PGA)

Data Dictionary

Control File

2. What is the primary purpose of the REDO log in Oracle?

Store archived transaction data

Record all database changes for recovery

Cache frequently accessed data

Manage user authentication

3. Which SQL statement is used to add a new column to an existing table?

INSERT COLUMN

ALTER TABLE ... ADD

CREATE COLUMN

MODIFY TABLE ... ADD

4. In PL/SQL, what keyword declares a variable that cannot be changed after initialization?

CONSTANT

IMMUTABLE

FIXED

FINAL

5. What is the correct syntax to create an index on a column named 'emp_id' in table 'employees'?

CREATE INDEX idx_emp ON employees(emp_id)

CREATE KEY idx_emp ON employees(emp_id)

CREATE INDEX employees.idx_emp(emp_id)

INDEX CREATE idx_emp ON employees(emp_id)

6. Which view contains information about all tables in the database?

USER_TABLES

ALL_TABLES

DBA_TABLES

SYSTEM_TABLES

7. What is a transaction in Oracle?

A single SQL statement

A sequence of SQL statements treated as a single unit of work

A backup operation

A user session

8. Which clause in a SELECT statement filters groups of rows based on aggregate functions?

WHERE

GROUP BY

HAVING

ORDER BY

9. What does ACID stand for in database transactions?

Atomicity, Consistency, Isolation, Durability

Accuracy, Clarity, Integrity, Data

Archive, Cache, Index, Deployment

Authentication, Compression, Indexing, Distribution

10. Which type of join returns all rows from both tables, including non-matching rows?

INNER JOIN

LEFT JOIN

FULL OUTER JOIN

CROSS JOIN

11. In Oracle, what is a cursor used for?

To point to a mouse location

To manage a set of rows returned by a query

To store encrypted passwords

To schedule batch jobs

12. Which parameter in the Oracle initialization file sets the maximum number of connections?

Processes

Connections

Max_sessions

User_connections

13. What is the purpose of the COMMIT statement in SQL?

To undo all changes in the transaction

To save changes permanently to the database

To delete all records

To create a backup

14. Which Oracle feature allows automatic backup of the database?

Recovery Manager (RMAN)

SQL*Plus

Data Pump

Enterprise Manager

15. What is a primary key constraint?

A column that allows duplicate and null values

A unique identifier that prevents null and duplicate values

A column that references another table

A column used for sorting data