Mixed Deck — All 1Z0-006 Topics Flashcards

Which background process is responsible for writing modified data blocks from the buffer cache to the data files?

Answer: DBWn

DBWn (Database Writer) writes dirty (modified) buffers from the database buffer cache to the data files on disk.

What is the System Change Number (SCN) used for in Oracle?

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.

What is the purpose of the Oracle Redo Log Buffer?

Answer: Holds redo entries before they are written to redo log files

The Redo Log Buffer is a circular buffer in the SGA that holds redo entries (change vectors) before LGWR flushes them to the online redo log files.

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

Answer: Second Normal Form (2NF)

2NF requires that every non-key attribute is fully functionally dependent on the entire primary key, eliminating partial dependencies.

What is the recommended approach when managing conflicting priorities in 1Z0-006?

Answer: Prioritize based on impact and urgency

Prioritizing based on impact and urgency ensures the most critical issues receive attention first while maintaining progress on other goals.

Which Oracle memory area is used for large operations such as parallel query buffers and RMAN backup/restore I/O?

Answer: Large Pool

The Large Pool is an optional SGA area that provides large memory allocations for parallel execution, RMAN, and shared server session memory.

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

Answer: Outcome-based performance indicators

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

In Third Normal Form (3NF), which type of dependency must be eliminated?

Answer: Transitive dependency of a non-key attribute on another non-key attribute

3NF requires that no non-key attribute depends on another non-key attribute (transitive dependency); all non-key attributes must depend directly on the primary key.

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

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.

Which type of integrity ensures that a foreign key value either matches an existing primary key value or is NULL?

Answer: Referential integrity

Referential integrity guarantees that every foreign key value references a valid primary key in the parent table or is NULL.

In data modeling terminology, what is a 'candidate key'?

Answer: A minimal set of attributes that can uniquely identify every row in a table

A candidate key is any minimal set of attributes that uniquely identifies each row; one candidate key is chosen as the primary key, and the rest become alternate keys.

What does 'referential integrity' mean in a relational database?

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.

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

Answer: Second Normal Form

Second Normal Form (2NF) addresses the issue of partial dependencies, which occur when a non-key attribute is dependent on only a part of a composite primary key. To achieve 2NF, these partially dependent attributes are moved to a new table with the relevant part of the composite key as its primary key. This process reduces data redundancy and improves data integrity within the database.

When a checkpoint occurs in Oracle, which process is signaled to write dirty buffers to disk?

Answer: DBWR

During a checkpoint, the CKPT process signals DBWR to write all dirty buffers to disk, then updates the control file and data file headers with the checkpoint position.

What is 'generalization' in EER modeling?

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.

What is an associative entity (also called an intersection entity)?

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.

In a SELECT statement, what is the correct order of these clauses: WHERE, FROM, SELECT, ORDER BY?

Answer: SELECT, FROM, WHERE, ORDER BY

Standard SQL clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Which aggregate function returns the number of non-NULL values in a column?

Answer: COUNT

COUNT(column_name) counts only non-NULL values in that column; COUNT(*) counts all rows including NULLs.

Which SQL clause is used to remove duplicate rows from a query result?

Answer: DISTINCT

The `DISTINCT` keyword in SQL is specifically used with the `SELECT` statement to retrieve only unique rows from a query result. It effectively eliminates any duplicate entries, ensuring that each returned row is unique based on the selected columns. Other clauses like `GROUP BY` are for aggregation, and `ORDER BY` is for sorting, not for removing duplicates.

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

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.