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

Question 1: Which of the following represents the correct hierarchy of logical storage structures in an Oracle database, from largest to smallest?

Tablespace > Extent > Segment > Oracle Block
Segment > Tablespace > Oracle Block > Extent
Tablespace > Segment > Extent > Oracle Block (Correct answer)
Oracle Block > Extent > Segment > Tablespace

Correct answer: Tablespace > Segment > Extent > Oracle Block

The logical hierarchy in Oracle is organized as follows: A database is divided into one or more Tablespaces. Each Tablespace contains Segments (e.g., a table or an index). Each Segment is made up of one or more Extents. Each Extent is a collection of contiguous Oracle Blocks, which are the smallest unit of I/O.

Question 2: 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)
CONNECT BY
HAVING
GROUP BY

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 3: What does 'referential integrity' mean in a relational database?

Foreign key values must match existing primary key values in the referenced table or be NULL (Correct answer)
Index entries must always match the actual table data
All column values must be stored in a normalized form
Every table must have a primary key defined

Correct answer: Foreign key values must match existing primary key values in the referenced table or be NULL

Referential integrity ensures that a foreign key value in a child table must either match an existing primary key value in the parent table or be NULL.

Question 4: Which audit trail type stores Oracle audit records in database tables within the AUDSYS schema?

XML Audit Trail
Unified Auditing Trail (Correct answer)
OS Audit Trail
Standard DB Audit Trail

Correct answer: Unified Auditing Trail

Unified Auditing, introduced in Oracle 12c, writes all audit records to the UNIFIED_AUDIT_TRAIL view stored in the AUDSYS schema.

Question 5: The process of applying archived redo logs to a standby database to keep it synchronized is known as:

Snapshot refresh
Checkpointing
Clustering
Managed recovery (Correct answer)

Correct answer: Managed recovery

Managed recovery is the process in Oracle Data Guard where archived redo logs are automatically applied to a physical standby database. This continuous application of redo logs keeps the standby database synchronized with the primary database, ensuring data consistency and readiness for failover or switchover operations.

Question 6: Which of the following is a key advantage of using views in Oracle Database?

Views store redundant copies of data for backup purposes
Views can simplify complex queries and restrict user access to specific columns or rows (Correct answer)
Views automatically create indexes on the underlying tables
Views always pre-compute results for faster retrieval

Correct answer: Views can simplify complex queries and restrict user access to specific columns or rows

Views simplify complex queries by encapsulating them as reusable named objects and can limit user exposure to only specific columns or rows of underlying tables, improving both usability and security.

Question 7: What is a 'surrogate key'?

A secondary key used when the primary key is unavailable
A system-generated unique identifier with no business meaning used as a primary key (Correct answer)
A natural key derived from real-world attributes of an entity
A key borrowed from another table to serve as a local primary key

Correct answer: A system-generated unique identifier with no business meaning used as a primary key

A surrogate key is an artificially created unique identifier (often an auto-incremented number) that has no intrinsic business meaning but serves as the primary key.

Question 8: What does the Oracle Data Dictionary store?

Application source code and compiled PL/SQL units
Undo segments for transaction rollback
Raw table data in compressed format
Metadata about database objects, users, and privileges (Correct answer)

Correct answer: Metadata about database objects, users, and privileges

The Oracle Data Dictionary stores metadata such as definitions of tables, views, indexes, users, and privileges.

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

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

Correct answer: DELETE FROM EMPLOYEES;

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

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

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)
Merging attributes from two entities into one

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 11: Which Oracle tool provides a graphical IDE for writing, debugging, and tuning SQL and PL/SQL code?

RMAN
SQL*Plus
Enterprise Manager Cloud Control
Oracle SQL Developer (Correct answer)

Correct answer: Oracle SQL Developer

Oracle SQL Developer is a free, graphical IDE that supports SQL querying, PL/SQL development, debugging, and performance tuning.

Question 12: What is an associative entity (also called an intersection entity)?

An entity shared between two separate databases
An entity that only has derived attributes
An entity created to represent a many-to-many relationship, containing foreign keys to both related entities (Correct answer)
An entity that stores aggregate summary data

Correct answer: An entity created to represent a many-to-many relationship, containing foreign keys to both related entities

An associative entity resolves a many-to-many relationship by acting as a junction, holding foreign keys to both parent entities and often adding its own attributes.

Question 13: A new accounting clerk needs to run a pre-written application that only reads data from the `invoices` and `payments` tables. According to the principle of least privilege, which of the following is the MOST appropriate set of privileges to grant?

The `DBA` role.
`SELECT` privilege on the `invoices` and `payments` tables. (Correct answer)
`SELECT`, `INSERT`, `UPDATE`, and `DELETE` privileges on the `invoices` and `payments` tables.
The `SELECT ANY TABLE` system privilege.

Correct answer: `SELECT` privilege on the `invoices` and `payments` tables.

The principle of least privilege dictates that a user should be granted only the minimum permissions necessary to perform their required tasks. Since the clerk only needs to read data from two specific tables, granting the `SELECT` privilege on only those two tables is the correct approach, minimizing potential security risks.

Question 14: What is 'generalization' in EER modeling?

Assigning foreign keys to enforce relationships
Defining a total participation constraint on a subclass
Breaking a single entity into multiple subclasses
Combining common attributes of multiple entity types into a higher-level superclass (Correct answer)

Correct answer: Combining common attributes of multiple entity types into a higher-level superclass

Generalization is a bottom-up process where shared attributes of multiple entity types are abstracted into a common superclass.

Question 15: Which SGA component is allocated per-server and stores session-specific data such as sort areas and cursor state?

System Global Area (SGA)
Redo log buffer
Shared pool
Program Global Area (PGA) (Correct answer)

Correct answer: Program Global Area (PGA)

The PGA (Program Global Area) is private memory allocated for each server process, storing session-specific data like sort work areas and bind variable values.

Question 16: Which statement about the WHERE clause is TRUE?

It can reference column aliases defined in the SELECT list
It can contain aggregate functions
It filters rows before any grouping occurs (Correct answer)
It is evaluated after GROUP BY

Correct answer: It filters rows before any grouping occurs

WHERE is evaluated before GROUP BY; it filters individual rows before aggregation takes place.

Question 17: Which Oracle constraint ensures that a column value cannot be NULL?

DEFAULT
UNIQUE
NOT NULL (Correct answer)
CHECK

Correct answer: NOT NULL

The NOT NULL constraint prevents a column from accepting NULL values, ensuring every row has a value for that column.

Question 18: In conceptual data modeling, what is the primary goal?

Write SQL CREATE TABLE statements
Optimize query execution plans
Define physical storage structures for the database
Capture business concepts and rules independent of any technology (Correct answer)

Correct answer: Capture business concepts and rules independent of any technology

A conceptual model captures high-level business entities, relationships, and rules without concern for how data will be physically stored or which DBMS will be used.

Question 19: Which professional attribute is most valued in data modeling within the 1Z0-006 field?

Accountability and commitment to standards (Correct answer)
Avoiding challenging situations
Working in isolation
Prioritizing personal convenience

Correct answer: Accountability and commitment to standards

Accountability and commitment to professional standards build trust and ensure consistent, high-quality practice.

Question 20: In Oracle SQL, which pseudo-table is used when you want to SELECT an expression or function without referencing a real table?

SYSDATE
SYS
ROWNUM
DUAL (Correct answer)

Correct answer: DUAL

DUAL is a special one-row, one-column dummy table in Oracle used to evaluate expressions that don't require a real table.

Question 21: What is a self-referencing (recursive) relationship in an ERD?

A relationship where an entity relates to itself (Correct answer)
A table that contains its own primary key as a default value
A relationship between two identical entities in different schemas
A relationship that loops back through multiple intermediate tables

Correct answer: A relationship where an entity relates to itself

A recursive relationship occurs when an entity has a relationship with itself, such as an EMPLOYEE who manages other EMPLOYEEs.

Question 22: Which clause in a SELECT statement is used to combine rows from two or more tables based on a related column?

JOIN ... ON (Correct answer)
COMBINE
MERGE
UNION

Correct answer: JOIN ... ON

JOIN ... ON links two tables by matching rows where the ON condition is true, producing a combined result set.

Question 23: Which data dictionary view shows all roles granted to a specific database user?

DBA_TAB_PRIVS
USER_ROLES
DBA_SYS_PRIVS
DBA_ROLE_PRIVS (Correct answer)

Correct answer: DBA_ROLE_PRIVS

DBA_ROLE_PRIVS lists all roles granted to users and other roles in the database.

Question 24: Which Oracle background process performs instance recovery automatically when the database is restarted after a crash?

DBWR
PMON
SMON (Correct answer)
CKPT

Correct answer: SMON

SMON (System Monitor) performs instance recovery at startup by applying redo logs and cleaning up temporary segments.

Question 25: What is the role of the Control File in an Oracle database?

Stores the compiled PL/SQL code for stored procedures
Contains the binary record of the database structure including data file locations and log file history (Correct answer)
Holds the parameter settings used at instance startup
Stores redo information for transaction recovery

Correct answer: Contains the binary record of the database structure including data file locations and log file history

The control file is a small binary file that records the database name, data file and redo log locations, checkpoint information, and RMAN backup history.

Question 26: What is the smallest unit of I/O in the Oracle database storage architecture?

A tablespace
An Oracle data block (Correct answer)
A segment
An extent

Correct answer: An Oracle data block

The Oracle data block is the smallest unit of storage that Oracle reads from and writes to disk, and its size is set at database creation.

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

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

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

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

Question 28: Which clause is required in every valid SELECT statement in Oracle?

ORDER BY
WHERE
FROM (Correct answer)
GROUP BY

Correct answer: FROM

Every SELECT statement requires a FROM clause to specify the data source (though DUAL can be used for expression-only queries).

Question 29: Which type of database relationship exists when one record in Table A can relate to many records in Table B, and one record in Table B can relate to many records in Table A?

One-to-many
Self-referencing
One-to-one
Many-to-many (Correct answer)

Correct answer: Many-to-many

A many-to-many relationship means multiple rows in one table associate with multiple rows in another, typically implemented using a junction (bridge) table.

Question 30: What is an Oracle Service Name used for?

Providing a logical alias that clients use to connect to a database or specific workload group (Correct answer)
Labeling archived redo log files for recovery identification
Identifying a specific background process within an instance
Naming the physical data files associated with a tablespace

Correct answer: Providing a logical alias that clients use to connect to a database or specific workload group

A service name is a logical identifier used in client connection strings; it can represent a single database, a PDB, or a subset of workloads for load balancing.

Question 31: Which Oracle background process performs instance recovery automatically when the database is restarted after a crash?

PMON
SMON (Correct answer)
DBWR
LGWR

Correct answer: SMON

SMON (System Monitor) performs instance recovery at startup by applying redo logs to roll forward and then rolling back uncommitted transactions.

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

LGWR
SMON
DBWR (Correct answer)
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 33: What is the System Change Number (SCN) used for in Oracle?

Tracking the number of active sessions
Identifying the current LGWR write cycle
Measuring SGA memory consumption
Providing a consistent ordering of database changes for recovery and read consistency (Correct answer)

Correct answer: Providing a consistent ordering of database changes for recovery and read consistency

The SCN is Oracle's internal timestamp that uniquely orders database changes, enabling consistent reads and precise point-in-time recovery.

Question 34: Which SQL command removes all rows from a table quickly without logging individual row deletions and cannot be rolled back by default in Oracle?

REMOVE
TRUNCATE (Correct answer)
DELETE
DROP

Correct answer: TRUNCATE

TRUNCATE removes all rows using a DDL operation that deallocates data pages; it cannot be rolled back because it does not generate per-row undo logs.

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

LGWR (Log Writer)
DBWR (Database Writer) (Correct answer)
PMON (Process Monitor)
SMON (System Monitor)

Correct answer: DBWR (Database Writer)

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

Question 36: What is the value of continuing education in data modeling for 1Z0-006 professionals?

It is primarily a social activity
It keeps professionals current with evolving standards and practices (Correct answer)
It is only needed for recertification
It replaces workplace experience

Correct answer: It keeps professionals current with evolving standards and practices

Continuing education ensures professionals stay current with the latest developments, standards, and best practices in their field.

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

No entity instance can belong to any subclass
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)
An entity instance can belong to more than one subclass simultaneously

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 38: Which statement BEST describes the relationship between Oracle Database Foundations Associate certification requirements and industry evolution?

Requirements become less stringent over time
Changes only occur when government mandates new requirements
Requirements evolve periodically to reflect advances in knowledge, technology, and practice standards (Correct answer)
Certification requirements never change once established

Correct answer: Requirements evolve periodically to reflect advances in knowledge, technology, and practice standards

Certification requirements evolve to keep pace with advances in professional knowledge, technological developments, and changes in practice standards. This ensures that certified professionals remain current and competent in a changing professional landscape.

Question 39: Oracle Database stores user passwords using which security technique to prevent plain-text storage?

RSA public-key encryption
Symmetric encryption with AES
Base64 encoding
One-way cryptographic hashing (Correct answer)

Correct answer: One-way cryptographic hashing

Oracle hashes passwords using a one-way algorithm so that even DBAs cannot retrieve the original password from the stored value.

Question 40: Which SQL clause filters rows AFTER a GROUP BY aggregation has been performed?

WHERE
HAVING (Correct answer)
QUALIFY
FILTER

Correct answer: HAVING

HAVING filters groups produced by GROUP BY, whereas WHERE filters individual rows before grouping occurs.

Question 41: What does the term 'Oracle instance' specifically refer to?

The tablespaces and their data files
The combination of SGA and background processes (Correct answer)
The set of physical files on disk
The user schemas and their objects

Correct answer: The combination of SGA and background processes

An Oracle instance is defined as the combination of the System Global Area (SGA) memory structure and the Oracle background processes.

Question 42: In Oracle, what does the REVOKE statement do?

Creates a new role with specified permissions
Temporarily suspends a user account
Grants additional privileges to a user
Removes previously granted privileges from a user or role (Correct answer)

Correct answer: Removes previously granted privileges from a user or role

REVOKE removes specific privileges or roles that were previously granted to a user or another role.

Question 43: What is the purpose of the Oracle AUDIT statement?

To track and record database activity for security analysis (Correct answer)
To enforce password complexity requirements
To restrict user access to specific schemas
To encrypt sensitive columns in a table

Correct answer: To track and record database activity for security analysis

The AUDIT statement enables auditing of specific SQL statements, schema objects, or privileges so the database records who did what and when.

Question 44: In Oracle, what is the difference between a system privilege and an object privilege?

System privileges are temporary; object privileges are permanent
System privileges allow performing actions on the database; object privileges allow actions on specific schema objects (Correct answer)
Object privileges can only be granted by the DBA; system privileges can be self-granted
System privileges apply to specific objects; object privileges apply to the whole database

Correct answer: System privileges allow performing actions on the database; object privileges allow actions on specific schema objects

System privileges (like CREATE TABLE) allow a user to perform database-wide actions, while object privileges (like SELECT on a specific table) control access to individual schema objects.

Question 45: Which of the following best describes 'cardinality ratio' in an ER relationship?

The number of attributes an entity can have
The maximum number of relationship instances in which an entity instance can participate (Correct answer)
The total number of entity types in a database schema
The minimum number of entities required to form a relationship

Correct answer: The maximum number of relationship instances in which an entity instance can participate

Cardinality ratio (1:1, 1:N, M:N) specifies the maximum number of relationship instances an entity can participate in on each side.

Question 46: Which constraint ensures that each value in a column is unique across the table but still allows NULL values?

UNIQUE (Correct answer)
PRIMARY KEY
NOT NULL
CHECK

Correct answer: UNIQUE

The UNIQUE constraint ensures no two rows have the same non-NULL value in the constrained column, but unlike PRIMARY KEY, it permits NULL values.

Question 47: In Oracle, which dictionary view would you query to see the privileges granted directly to a specific user (not through roles)?

DBA_ROLE_PRIVS
DBA_SYS_PRIVS (Correct answer)
USER_TAB_PRIVS
SESSION_PRIVS

Correct answer: DBA_SYS_PRIVS

DBA_SYS_PRIVS shows system privileges granted directly to users and roles, allowing a DBA to see which system privileges a user holds.

Question 48: Which SQL function converts a string to uppercase?

INITCAP
LOWER
TRIM
UPPER (Correct answer)

Correct answer: UPPER

UPPER(string) returns the string with all characters converted to uppercase letters.

Question 49: In Oracle, what is Automatic Memory Management (AMM)?

A feature where Oracle automatically manages OS swap space
A mode where Oracle automatically distributes memory between SGA and PGA based on workload (Correct answer)
An Oracle feature that automatically extends tablespace when full
A method of automatically compressing data files to save disk space

Correct answer: A mode where Oracle automatically distributes memory between SGA and PGA based on workload

AMM allows Oracle to automatically tune the total memory (SGA + PGA) allocation between components based on current workload demand.

Question 50: Which Oracle command immediately terminates a user's session?

DISCONNECT SESSION
ALTER SYSTEM KILL SESSION (Correct answer)
DROP SESSION
KILL SESSION

Correct answer: ALTER SYSTEM KILL SESSION

ALTER SYSTEM KILL SESSION with the session ID and serial number terminates a user's database session immediately.

Question 51: Which command permanently saves all outstanding transactions in an Oracle database?

SAVEPOINT
FLASHBACK
ROLLBACK
COMMIT (Correct answer)

Correct answer: COMMIT

The `COMMIT` command in SQL is used to make all changes performed within the current transaction permanent in the database. Once committed, the changes cannot be undone by a `ROLLBACK` statement, and they become visible to other database users. This ensures data integrity and consistency by finalizing a set of operations, making them a permanent part of the database.

Question 52: Which of the following is TRUE about a recursive (unary) relationship?

It can only have a 1:1 cardinality
It involves exactly two different entity types
It is a relationship where an entity type is associated with itself (Correct answer)
It cannot be represented in a relational schema

Correct answer: It is a relationship where an entity type is associated with itself

A unary (recursive) relationship associates instances of a single entity type with each other, such as an Employee who manages other Employees.

Question 53: Which relationship type links a weak entity to its owner entity in an ER diagram?

Ternary relationship
Identifying relationship (Correct answer)
Non-identifying relationship
Recursive relationship

Correct answer: Identifying relationship

An identifying relationship (drawn as a double diamond) connects a weak entity to its owner entity, providing the context needed for identification.

Question 54: Which Oracle network component resolves a service name (like 'ORCL') to a database host, port, and service name?

tnsnames.ora (Correct answer)
listener.ora
sqlnet.ora
Oracle Net Listener

Correct answer: tnsnames.ora

The tnsnames.ora file contains named connection descriptors that map service aliases to host, port, and Oracle service name for client connections.

Question 55: Which concept ensures that every row in a table can be uniquely identified?

Index
Foreign key
Constraint
Primary key (Correct answer)

Correct answer: Primary key

A primary key is a column or combination of columns whose values uniquely identify each row in a table and cannot be NULL.

Question 56: What does multiplexing the control file mean in Oracle?

Keeping multiple identical copies of the control file in different locations (Correct answer)
Compressing the control file to save space
Encrypting the control file for security
Splitting the control file across multiple tablespaces

Correct answer: Keeping multiple identical copies of the control file in different locations

Multiplexing the control file means maintaining two or more identical copies on separate disk locations so that no single disk failure can destroy all copies.

Question 57: Which of the following is an example of a multivalued attribute in an ERD?

Employee's phone numbers (can have several) (Correct answer)
Employee's salary
Employee's date of birth
Employee's department ID

Correct answer: Employee's phone numbers (can have several)

A multivalued attribute can hold more than one value for a single entity instance, such as an employee having multiple phone numbers.

Question 58: A database alert log records:

Only user queries
All DML statements
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 59: What does the Oracle REVOKE statement do?

Removes a previously granted privilege from a user or role (Correct answer)
Transfers privileges between users
Disables a user account temporarily
Creates a new privilege

Correct answer: Removes a previously granted privilege from a user or role

REVOKE removes system or object privileges that were previously granted to a user, role, or PUBLIC.

Question 60: Which relationship type in an ERD is represented by a line with crow's-foot notation on both ends?

Many-to-many (Correct answer)
One-to-one
One-to-many
Self-referencing

Correct answer: Many-to-many

Crow's-foot notation on both ends of a relationship line indicates a many-to-many relationship between the two entities.

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