Oracle Database Foundations Associate (1Z0-006) — Questions and Answers

Question 1: Which Oracle memory structure caches recently accessed data blocks?

Redo Log Buffer
Database Buffer Cache (Correct answer)
Shared Pool
Large Pool

Correct answer: Database Buffer Cache

The Database Buffer Cache is a critical component of the System Global Area (SGA) in Oracle, used to store copies of data blocks read from data files. Its purpose is to reduce physical I/O by caching frequently accessed data, allowing subsequent requests for the same data to be served directly from memory, which is much faster.

Question 2: What is the primary purpose of database normalization?

To simplify backup and recovery operations
To maximize query performance by denormalizing data
To reduce data redundancy and improve data integrity (Correct answer)
To increase the number of tables for better indexing

Correct answer: To reduce data redundancy and improve data integrity

Normalization organizes data to minimize redundancy and dependency, preventing update, insert, and delete anomalies.

Question 3: What does granting a privilege WITH GRANT OPTION allow the recipient to do?

Grant the same object privilege to other users (Correct answer)
Use the privilege indefinitely without expiration
Bypass all audit policies related to that privilege
Perform the action without generating redo logs

Correct answer: Grant the same object privilege to other users

WITH GRANT OPTION lets the grantee pass the same object privilege on to additional users, creating a chain of privilege delegation.

Question 4: In Oracle, which structure maps logical database blocks to physical file locations?

Control file
Parameter file
Redo log
Extent map in segment header (Correct answer)

Correct answer: Extent map in segment header

In Oracle, a segment (e.g., a table or index) is composed of extents, which are contiguous blocks of data. The segment header contains an extent map that tracks the physical file and block locations of all the extents belonging to that segment. This map is crucial for the database to efficiently locate and access the data stored within a segment, linking logical data structures to their physical storage.

Question 5: In Oracle's memory architecture, which area is shared by all sessions connected to the same instance?

UGA (User Global Area) in dedicated mode
PGA (Program Global Area)
SGA (System Global Area) (Correct answer)
Sort work area

Correct answer: SGA (System Global Area)

The SGA is a shared memory region allocated when the instance starts and is accessible by all background and server processes of that instance.

Question 6: Which notation style uses 'crow's foot' symbols to indicate cardinality on an ER diagram?

Chen notation
Crow's Foot (IE) notation (Correct answer)
Bachman notation
UML class diagram notation

Correct answer: Crow's Foot (IE) notation

Crow's Foot (also called Information Engineering notation) uses a crow's foot symbol to denote the 'many' side of a relationship.

Question 7: Which SQL command is used to permanently save changes made during a transaction?

PERSIST
SAVE
COMMIT (Correct answer)
ROLLBACK

Correct answer: COMMIT

COMMIT permanently saves all changes made during the current transaction to the database.

Question 8: Which normal form eliminates partial dependencies on a composite primary key?

First Normal Form (1NF)
Boyce-Codd Normal Form (BCNF)
Third Normal Form (3NF)
Second Normal Form (2NF) (Correct answer)

Correct answer: Second Normal Form (2NF)

2NF removes partial dependencies, ensuring every non-key attribute depends on the whole composite primary key, not just part of it.

Question 9: Which term describes the process of organizing database tables to reduce data redundancy and improve data integrity?

Replication
Partitioning
Normalization (Correct answer)
Indexing

Correct answer: Normalization

Normalization is the process of structuring a relational database according to normal forms (1NF, 2NF, 3NF, etc.) to eliminate redundancy and dependency anomalies.

Question 10: What is the purpose of a junction (bridge) table in relational database design?

To hold temporary data during transaction processing
To store indexes for faster query performance
To resolve a many-to-many relationship into two one-to-many relationships (Correct answer)
To enforce referential integrity constraints automatically

Correct answer: To resolve a many-to-many relationship into two one-to-many relationships

A junction table sits between two entities in a many-to-many relationship, holding foreign keys to both and converting the relationship into two one-to-many links.

Question 11: What is the difference between DELETE and TRUNCATE in Oracle?

TRUNCATE allows a WHERE clause; DELETE does not
DELETE keeps the table; TRUNCATE drops the table entirely
TRUNCATE cannot be rolled back; DELETE can be rolled back before COMMIT (Correct answer)
DELETE is DDL; TRUNCATE is DML

Correct answer: TRUNCATE cannot be rolled back; DELETE can be rolled back before COMMIT

TRUNCATE is a DDL operation that cannot be rolled back; DELETE is DML and can be undone with ROLLBACK before committing.

Question 12: Which SQL statement is used to permanently save changes made during a transaction?

COMMIT (Correct answer)
SAVE
SAVEPOINT
ROLLBACK

Correct answer: COMMIT

COMMIT permanently writes all pending DML changes to the database, making them visible to other sessions.

Question 13: Which concept refers to hiding the internal complexity of a database from end users and applications?

Replication
Partitioning
Normalization
Abstraction (Correct answer)

Correct answer: Abstraction

Data abstraction allows users and applications to interact with data at a high level without needing to know how it is physically stored or organized internally.

Question 14: A user connects to an Oracle database and executes a large, complex SQL query that requires significant sorting and hashing operations. Where is the memory for these user-specific operations primarily allocated?

Database Buffer Cache
Program Global Area (PGA) (Correct answer)
Shared Pool
Redo Log Buffer

Correct answer: Program Global Area (PGA)

The Program Global Area (PGA) is a private memory region that contains data and control information for a server process. It is allocated when a server process starts and is used for session-specific tasks like sorting, hashing, and storing bind variable values. The other options are components of the System Global Area (SGA), which is shared among all user processes.

Question 15: Which view would a DBA query to see all current active sessions in an Oracle database?

DBA_SESSIONS
V$SESSION (Correct answer)
ALL_SESSIONS
USER_SESSIONS

Correct answer: V$SESSION

V$SESSION is a dynamic performance view that shows information about all current sessions connected to the Oracle database.

Question 16: In Oracle Database architecture, what is a tablespace?

A logical storage unit composed of one or more data files (Correct answer)
A background process that manages storage allocation
A memory area in the SGA used for sorting
A single physical file stored on the operating system

Correct answer: A logical storage unit composed of one or more data files

A tablespace is a logical storage container that groups related database objects; it maps to one or more physical data files on disk.

Question 17: Which initialization parameter controls the maximum number of user sessions that can connect to an Oracle instance simultaneously?

DB_CONNECTIONS
MAX_USERS
MAX_CONNECTIONS
SESSIONS (Correct answer)

Correct answer: SESSIONS

The SESSIONS initialization parameter sets the maximum number of concurrent sessions allowed in an Oracle instance.

Question 18: In 1Z0-006 practice, what is the best approach to quality improvement in data modeling?

Wait for problems to occur before acting
Make changes without measuring results
Use data-driven methods with measurable outcomes (Correct answer)
Copy what other organizations do without analysis

Correct answer: Use data-driven methods with measurable outcomes

Data-driven quality improvement with measurable outcomes ensures that changes actually produce the intended improvements and can be verified.

Question 19: Which DELETE syntax correctly removes all rows from a table named EMPLOYEES?

REMOVE FROM EMPLOYEES;
DROP EMPLOYEES;
DELETE FROM EMPLOYEES; (Correct answer)
DELETE EMPLOYEES;

Correct answer: DELETE FROM EMPLOYEES;

DELETE FROM table_name without a WHERE clause removes all rows while keeping the table structure intact.

Question 20: A user wants to add a new employee record to the 'employees' table, which has columns for 'employee_id', 'last_name', and 'hire_date'. Which of the following SQL statements is the correct syntax for this operation?

UPDATE employees SET employee_id=101, last_name='Smith', hire_date='17-OCT-23';
CREATE NEW ROW IN employees VALUES (101, 'Smith', '17-OCT-23');
ADD INTO employees VALUES (101, 'Smith', '17-OCT-23');
INSERT INTO employees (employee_id, last_name, hire_date) VALUES (101, 'Smith', '17-OCT-23'); (Correct answer)

Correct answer: INSERT INTO employees (employee_id, last_name, hire_date) VALUES (101, 'Smith', '17-OCT-23');

The correct syntax for adding a new row of data to a table is the `INSERT INTO` statement. It specifies the table, optionally the columns, and then the `VALUES` clause provides the data for the new row.

Question 21: What is the role of the Oracle Listener process?

Manages memory allocation for sessions
Writes redo log entries to disk
Performs automatic backup operations
Accepts incoming client connection requests to the database (Correct answer)

Correct answer: Accepts incoming client connection requests to the database

The Oracle Listener is a network process that listens on a port and routes incoming client connection requests to the appropriate database instance.

Question 22: Which type of JOIN returns all rows from the left table and matching rows from the right table, with NULLs for non-matching right rows?

LEFT OUTER JOIN (Correct answer)
CROSS JOIN
RIGHT OUTER JOIN
INNER JOIN

Correct answer: LEFT OUTER JOIN

A LEFT OUTER JOIN returns all rows from the left table plus matching rows from the right table; unmatched right-table columns appear as NULL.

Question 23: What happens to a user's objects when the user account is DROPPED in Oracle?

Objects are archived to a backup tablespace
All objects owned by the user are also dropped (Correct answer)
Objects are retained but become inaccessible
Objects are moved to the SYSTEM schema

Correct answer: All objects owned by the user are also dropped

Dropping a user with the CASCADE option removes the user and all their schema objects from the database.

Question 24: An application schema owner should not log in directly. Which account status best enforces this policy?

OPEN with limited privileges
EXPIRED
LOCKED
EXPIRED & LOCKED (Correct answer)

Correct answer: EXPIRED & LOCKED

Setting an account to EXPIRED & LOCKED prevents direct logins while still allowing the schema to exist and be accessed by other authorized application accounts.

Question 25: What is the default tablespace for a new Oracle user if none is specified at creation?

USERS
SYSAUX
SYSTEM (Correct answer)
TEMP

Correct answer: SYSTEM

If no default tablespace is specified when creating a user, Oracle assigns the SYSTEM tablespace as the default, which is generally undesirable.

Question 26: Which of the following SQL statements modifies existing data in a table?

INSERT
MERGE
UPDATE (Correct answer)
ALTER

Correct answer: UPDATE

UPDATE changes the values of existing rows in a table based on the specified SET and optional WHERE conditions.

Question 27: Which SQL statement is used to add a new row to a table?

INSERT (Correct answer)
MERGE
ALTER
UPDATE

Correct answer: INSERT

The `INSERT` statement is the standard SQL command used to add one or more new rows (records) into a table. It specifies the table name and the values to be inserted into its columns. This is distinct from `UPDATE`, which modifies existing rows, or `ALTER`, which modifies the table structure itself.

Question 28: An entity that cannot be uniquely identified by its own attributes alone and depends on another entity is called a:

Associative entity
Strong entity
Derived entity
Weak entity (Correct answer)

Correct answer: Weak entity

A weak entity lacks sufficient attributes to form its own primary key and relies on its owner (strong) entity for full identification.

Question 29: In Oracle SQL Developer Data Modeler, what is the difference between a 'logical' and a 'relational' model?

A relational model is identical to a conceptual model
A logical model includes physical storage; a relational model is DBMS-agnostic
A logical model uses entity/attribute terminology; a relational model maps these to tables/columns with keys (Correct answer)
A logical model can only represent one-to-one relationships

Correct answer: A logical model uses entity/attribute terminology; a relational model maps these to tables/columns with keys

The logical model uses abstract entity and attribute concepts, while the relational model transforms these into tables, columns, primary keys, and foreign keys for implementation.

Question 30: In a dedicated server configuration, how many server processes handle a single user session?

Multiple DBWR processes per session
One dedicated server process per session (Correct answer)
One shared server process handles all sessions
One background process per session

Correct answer: One dedicated server process per session

In dedicated server mode, Oracle spawns one dedicated server process exclusively for each user session for its entire duration.

Question 31: Which clause in a SELECT statement is used to filter rows AFTER grouping has been applied?

ORDER BY
WHERE
GROUP BY
HAVING (Correct answer)

Correct answer: HAVING

HAVING filters groups after GROUP BY aggregation, while WHERE filters individual rows before grouping.

Question 32: Which Oracle tool would a DBA use to interactively diagnose performance issues by analyzing wait events, top SQL, and session activity in real time?

Enterprise Manager Performance Hub (Correct answer)
Oracle SQL Developer's DBA panel
RMAN
Data Pump

Correct answer: Enterprise Manager Performance Hub

The Enterprise Manager Performance Hub (available in EM Express and Cloud Control) provides real-time and historical performance analysis including ASH, ADDM, and top SQL.

Question 33: Which Oracle background process writes dirty buffers from the database buffer cache to data files?

SMON
DBWR (Correct answer)
LGWR
PMON

Correct answer: DBWR

DBWR (Database Writer) is responsible for writing modified (dirty) data blocks from the buffer cache to the data files on disk.

Question 34: Which of the following best describes 'total specialization'?

Every superclass instance must belong to at least one subclass (Correct answer)
Every subclass must have a discriminating attribute
The superclass cannot have its own instances
Subclasses are mutually exclusive

Correct answer: Every superclass instance must belong to at least one subclass

Total specialization (double line in EER) means every entity in the superclass must also be a member of at least one subclass.

Question 35: What does SQL*Plus primarily allow a DBA or developer to do?

Execute SQL and PL/SQL statements interactively from a command line (Correct answer)
Monitor real-time AWR performance reports
Visually browse and edit table data in a spreadsheet view
Graphically design database schemas

Correct answer: Execute SQL and PL/SQL statements interactively from a command line

SQL*Plus is Oracle's command-line interface for running SQL queries, PL/SQL blocks, and administrative commands against an Oracle database.

Question 36: Which SQL clause is used to join data from a column with the rows of another table inline?

SUBQUERY in FROM (inline view) (Correct answer)
GROUP BY
CONNECT BY
HAVING

Correct answer: SUBQUERY in FROM (inline view)

A subquery in the FROM clause (inline view) acts as a derived table that can be queried like a regular table.

Question 37: A table stores OrderID, ProductID, ProductName, and Quantity. OrderID+ProductID form the primary key. ProductName depends only on ProductID. Which normal form is violated?

3NF, because of a transitive dependency
1NF, because of repeating groups
BCNF, because a non-key attribute determines a key attribute
2NF, because ProductName is partially dependent on the composite key (Correct answer)

Correct answer: 2NF, because ProductName is partially dependent on the composite key

ProductName depends only on ProductID (part of the composite key), creating a partial dependency that violates 2NF.

Question 38: When modeling a database, you identify a many-to-many relationship between 'STUDENT' and 'COURSE'. To resolve this relationship in the logical model, what should be created?

A supertype entity
An intersection entity (associative entity) (Correct answer)
A foreign key in the 'STUDENT' table
A recursive relationship

Correct answer: An intersection entity (associative entity)

Many-to-many relationships are resolved by creating an intersection entity, also known as an associative entity or junction table. This new entity will have a many-to-one relationship with each of the original entities, typically containing foreign keys that reference the primary keys of the 'STUDENT' and 'COURSE' tables.

Question 39: Which Oracle constraint type can reference columns in the same row to enforce a business rule, such as END_DATE >= START_DATE?

NOT NULL
CHECK (Correct answer)
PRIMARY KEY
FOREIGN KEY

Correct answer: CHECK

A CHECK constraint can compare columns within the same row and enforce business rules like END_DATE >= START_DATE.

Question 40: Which foundational principle is MOST important for success in the Oracle Database Foundations Associate profession?

Specializing in only one narrow area of practice
Maintaining the minimum requirements for certification
Maximizing financial returns on every engagement
Commitment to continuous learning, ethical practice, and quality outcomes (Correct answer)

Correct answer: Commitment to continuous learning, ethical practice, and quality outcomes

Success in any professional field requires a commitment to continuous learning to stay current, ethical practice to maintain trust and integrity, and a focus on quality outcomes that serve stakeholders and the public interest.

Question 41: In data modeling, what does 'domain' refer to?

The set of all valid values an attribute can hold (Correct answer)
A group of related tables in a schema
The network address of the database server
The physical storage location of table data

Correct answer: The set of all valid values an attribute can hold

A domain defines the permissible set of values for an attribute, including data type, length, format, and constraints.

Question 42: Which privilege is required to query data from another user's table in Oracle without any additional grants?

DBA role only
PUBLIC SELECT
CREATE TABLE
SELECT ANY TABLE system privilege (Correct answer)

Correct answer: SELECT ANY TABLE system privilege

The SELECT ANY TABLE system privilege allows a user to query any table in the database without needing an explicit object grant from the owner.

Question 43: Which Oracle startup mode allows the DBA to perform maintenance but prevents regular users from connecting?

NOMOUNT
MOUNT
OPEN RESTRICTED (Correct answer)
OPEN READ ONLY

Correct answer: OPEN RESTRICTED

OPEN RESTRICTED mode opens the database but limits access to users with the RESTRICTED SESSION privilege, allowing DBA maintenance tasks.

Question 44: In EER (Enhanced Entity-Relationship) modeling, 'specialization' is best defined as:

Merging attributes from two entities into one
Creating a relationship between two different database schemas
Combining multiple entity types into a single superclass
Defining subclasses of an entity type based on distinguishing characteristics (Correct answer)

Correct answer: Defining subclasses of an entity type based on distinguishing characteristics

Specialization is a top-down process of defining subclasses from a superclass based on specific distinguishing features.

Question 45: A database alert log records:

All DML statements
Only user queries
Critical messages and errors from background processes (Correct answer)
Operating system patches

Correct answer: Critical messages and errors from background processes

The database alert log is a chronological record of critical messages, errors, and significant events generated by Oracle background processes. It provides vital information for diagnosing database issues, tracking startup/shutdown events, and monitoring internal operations, making it an essential diagnostic tool for DBAs.

Question 46: Which term describes a candidate key that was NOT chosen as the primary key?

Surrogate key
Composite key
Foreign key
Alternate key (Correct answer)

Correct answer: Alternate key

An alternate key is any candidate key that was not selected as the primary key but still uniquely identifies each row.

Question 47: What is the difference between a dedicated server and a shared server connection in Oracle?

Shared server stores session state in the SGA; dedicated server stores it in a temp file
In dedicated server, each session has its own server process; in shared server, multiple sessions share server processes (Correct answer)
Dedicated server only supports read-only connections; shared server allows writes
Dedicated server uses TCP; shared server uses named pipes

Correct answer: In dedicated server, each session has its own server process; in shared server, multiple sessions share server processes

With dedicated server, Oracle spawns one server process per user session; with shared server (MTS), a pool of server processes handles requests from many sessions.

Question 48: Which relational operation returns only rows that appear in both of two query result sets?

MINUS
JOIN
INTERSECT (Correct answer)
UNION

Correct answer: INTERSECT

INTERSECT returns only the rows that are common to both result sets, eliminating rows that appear in only one.

Question 49: Which of the following correctly inserts a row with specific column values?

ADD INTO EMPLOYEES (id, name) VALUES (101, 'Ana');
INSERT INTO EMPLOYEES SET id=101, name='Ana';
INSERT INTO EMPLOYEES VALUES (101, 'Ana'); (Correct answer)
INSERT EMPLOYEES VALUES (101, 'Ana');

Correct answer: INSERT INTO EMPLOYEES VALUES (101, 'Ana');

The correct Oracle syntax is INSERT INTO table_name [(columns)] VALUES (values).

Question 50: A PRODUCT table has columns: ProductID, CategoryID, CategoryName, Price. CategoryName depends on CategoryID, not directly on ProductID. Which normal form violation does this represent?

1NF violation due to non-atomic values
2NF violation due to a partial dependency
BCNF violation due to a non-superkey determinant
3NF violation due to a transitive dependency (Correct answer)

Correct answer: 3NF violation due to a transitive dependency

CategoryName transitively depends on ProductID through CategoryID (ProductID → CategoryID → CategoryName), which violates 3NF.

Question 51: A database administrator is performing a recovery operation and needs to locate all the data files and redo log files. Which physical database structure contains this critical metadata, including the database name and the timestamp of its creation?

Data Files
Password File
Control Files (Correct answer)
Parameter File (PFILE/SPFILE)

Correct answer: Control Files

Control files are small binary files that record the physical structure of the database. They contain the database name, the names and locations of data files and redo log files, and the database creation timestamp. They are essential for mounting and opening the database.

Question 52: What is Transparent Data Encryption (TDE) in Oracle used for?

Encrypting data stored in data files and backups without application changes (Correct answer)
Hashing user passwords stored in the database
Encrypting network traffic between the client and server
Masking sensitive data displayed in query results

Correct answer: Encrypting data stored in data files and backups without application changes

TDE encrypts data at rest in data files and tablespaces, protecting against physical theft of storage media without requiring any application changes.

Question 53: A 'disjoint' specialization constraint in EER means:

No entity instance can belong to any subclass
An entity instance can belong to more than one subclass simultaneously
All entity instances must belong to at least one subclass
An entity instance can belong to at most one subclass at a time (Correct answer)

Correct answer: An entity instance can belong to at most one subclass at a time

A disjoint constraint (marked 'd' in EER diagrams) means the subclasses are mutually exclusive — an entity can be in only one subclass.

Question 54: When an ER diagram shows a relationship with an attribute (e.g., 'Date' on a WORKS_FOR relationship), what does this indicate?

The attribute describes a property of the relationship itself, not either entity alone (Correct answer)
The attribute is a primary key of a new entity
The attribute is derived and should not be stored
The attribute belongs to one of the participating entities

Correct answer: The attribute describes a property of the relationship itself, not either entity alone

Relationship attributes capture information that only makes sense in the context of the association between two entities, not either entity individually.

Question 55: Which command would a DBA use to change a user's password in Oracle?

UPDATE DBA_USERS SET PASSWORD = 'new_password'
ALTER USER username IDENTIFIED BY new_password (Correct answer)
MODIFY USER username PASSWORD new_password
RESET PASSWORD FOR username

Correct answer: ALTER USER username IDENTIFIED BY new_password

The ALTER USER ... IDENTIFIED BY statement is the correct SQL syntax to change a user's password in Oracle.

Question 56: To revoke a role from a user, which statement is used?

REVOKE (Correct answer)
DENY
DROP
REMOVE

Correct answer: REVOKE

The REVOKE statement is used in SQL to remove previously granted privileges or roles from a user or another role. It is the counterpart to the GRANT statement, allowing database administrators to manage and restrict access permissions. For example, `REVOKE CONNECT FROM username;`.

Question 57: Which profile parameter limits the number of consecutive failed login attempts before an account is automatically locked?

PASSWORD_LOCK_TIME
PASSWORD_REUSE_TIME
FAILED_LOGIN_ATTEMPTS (Correct answer)
LOGIN_RETRIES

Correct answer: FAILED_LOGIN_ATTEMPTS

FAILED_LOGIN_ATTEMPTS in a profile defines how many consecutive incorrect passwords are allowed before Oracle locks the account.

Question 58: What does the NVL(commission_pct, 0) function do when commission_pct is NULL?

Returns the string 'NULL'
Raises an exception
Returns 0 (Correct answer)
Leaves the value unchanged

Correct answer: Returns 0

NVL replaces a NULL value with the specified substitute, so NVL(commission_pct, 0) returns 0 when commission_pct is NULL.

Question 59: Which metric is most useful for evaluating program effectiveness in 1Z0-006?

Amount of money spent
Number of staff involved
Number of meetings held
Outcome-based performance indicators (Correct answer)

Correct answer: Outcome-based performance indicators

Outcome-based performance indicators directly measure whether the program is achieving its intended results and goals.

Question 60: Following an unexpected server shutdown, an Oracle database instance is restarted. The database automatically begins a process of rolling forward committed changes from the redo logs and rolling back uncommitted transactions. Which background process is primarily responsible for performing this crash recovery?

Archive (ARCn)
Process Monitor (PMON)
Database Writer (DBWn)
System Monitor (SMON) (Correct answer)

Correct answer: System Monitor (SMON)

The System Monitor (SMON) process is responsible for instance recovery, also known as crash recovery. Upon startup after a failure, SMON applies redo log entries to the data files for committed transactions and rolls back uncommitted transactions. PMON cleans up failed user processes, and ARCn handles the archiving of redo logs.

Oracle Database Foundations Associate (1Z0-006)

This certification validates foundational knowledge of Oracle Database concepts, including SQL, database architecture, and basic administration.

Questions

90 min

Time Limit

65%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds