Oracle Database Fundamentals and SQL

A check constraint restricts the values that can be entered into a column. It must be defined as a fixed condition during table creation or alteration. Check constraints cannot reference external lookup tables, pseudo-columns like SYSDATE, or enforce non-null values directly without specific syntax.

The LOCK transaction control prevents concurrent users from updating the same data simultaneously. Unlike commit (save changes), rollback (undo changes), or savepoint (mark a rollback point), lock actually restricts access to prevent conflicts during multi-user scenarios. It ensures data consistency by serializing access to the data.

The MONTHS_BETWEEN function calculates the number of months between two dates, returning a numeric value rather than a DATE datatype. Functions like NEW_TIME, LAST_DAY, and ADD_MONTHS all perform date calculations and return a DATE datatype, making the numeric function the correct exception.

In standard SQL operator precedence, multiplication (*) and division (/) have higher precedence than addition (+) and subtraction (-). When operators have equal precedence (like * and /), they are evaluated left-to-right. Among the options, * will be evaluated first assuming it appears before / in the expression.

CONCAT can return non-NULL when passed NULL because it concatenates non-NULL values and ignores NULL arguments. For example, CONCAT('abc', NULL) returns 'abc'. LENGTH(NULL) returns NULL. INSTR with NULL returns NULL. NULLIF returns NULL if its arguments are equal, otherwise the first argument.

BFILE is a unique Oracle datatype that stores a file pointer to data located outside the database in the operating system filesystem. Unlike BLOB and CLOB types which store data within the database, BFILE only maintains a reference to external files. UROWID is for rowids, NCLOB stores character large objects within the database.

Unique key constraints allow NULL values, whereas primary key constraints strictly forbid NULL values in their columns. Additionally, a pre-existing non-unique index can be used to enforce a primary key constraint if created beforehand. This makes the marked true options correct and the other choices false.

When joining three tables, you need at least 2 join conditions to avoid a Cartesian product. Each join condition connects two tables, so for N tables you need N-1 join conditions. With only 1 join condition for 3 tables, the third table would not be properly linked, creating a Cartesian product with the unjoined portion.

ASM (Automatic Storage Management) is Oracle's storage management solution that combines both a file system and a volume manager. It simplifies database storage by managing disk space and file placement automatically.

The Cluster Synchronization Services (CSS) daemon is required to synchronize ASM and database instances. Note that the actual executable process name is ocsssd (or ocsssd.bin), but the name of the service/daemon itself is CSS.

Trace files are not supported in ASM because they require frequent writes and are diagnostic in nature. ASM supports database files, control files, redo logs, and other persistent database-related files.

In ASM, mirroring is performed at the extent level (not file, block, or tablespace). Each extent is a unit of allocation (typically 1MB), and ASM can mirror these extents across multiple failure groups for redundancy.

ASM divides files into 1MB extents by default (though this is configurable). These extents are the basic units of allocation and are striped across disks in the disk group for performance and redundancy.

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data by managing standby databases. Mirroring is a storage-level concept, data refresh is for copy management, and security layers protect access, none of which define Data Guard's core purpose.

An Oracle Data Guard primary database serves as the production database and can be configured as either a single-instance standalone database or a multi-node Real Application Clusters (RAC) database. It is not limited to only RAC or standalone, and a physical standby is a backup copy, not the production database.

The three core components of Oracle Data Guard are Redo Transport Services (transferring redo data), Apply Services (applying redo data to standby databases), and Role Transitions (switchover and failover). Failover is a specific transition event, not an independent service category like redo transport or apply services.

DGMGRL (Data Guard Manager) is Oracle's command-line interface for managing Data Guard configurations. It allows administrators to perform operations like creating standby databases, managing broker configurations, and monitoring the Data Guard environment through SQL-like commands.

Oracle Data Guard supports three data protection modes: Maximum Protection, Maximum Availability, and Maximum Performance. These modes allow administrators to balance data protection requirements against transactional performance. Other options include terms like scalability or generic protection, which are not official Data Guard protection modes.

The Shared Pool in the SGA contains the Library Cache, which itself contains the dictionary cache storing data dictionary information. Buffer cache stores database blocks, and PGA is private memory per server process.

Oracle can start with just one control file, though production databases should multiplex control files (maintain multiple copies) for redundancy. The control file contains critical metadata like database name, file locations, and checkpoint information.