Oracle Practice Test - 26

To join 3 tables without a Cartesian product, you need 2 join conditions. For N tables, you need N-1 join conditions. With 3 tables (A, B, C), you'd join A-B and B-C with 2 separate conditions.

A right outer join returns all rows from the right table (B) and matching rows from the left table (A). Left outer join returns all from A, not B. Inner join returns only matching rows from both. Cross join creates a Cartesian product.

When a replication site pulls changes from another site, it's called snapshot replication. The snapshot site requests and pulls data refreshes from the master site. Master sites push changes, snapshot sites pull them.

Tablespaces are created using the CREATE TABLESPACE command in SQL. ALTER DATABASE and ALTER SYSTEM modify existing structures. The 'create' command is the standard DDL statement for creating new database objects like tablespaces.

Cluster tables allow rows from multiple tables to be stored in the same data block based on a cluster key. This improves join performance for frequently joined tables. Partitioned tables split a single table, unclustered is the default, and index-organized tables store data within the index structure.

REF cursors in PL/SQL are cursor variables that can be passed between procedures and functions. Unlike static cursors, REF cursors are dynamic and can be opened for different queries at runtime and shared across program units.

A hot backup is taken while the database is online and running, allowing users to continue working during backup. This contrasts with cold backup, which requires shutting down the database. The key characteristic is that the database remains operational.

A maximum of one undo alert is received during a 24-hour period.

TO_CHAR converts a date value to a string with the specified format mask. The 'yyyy' format element extracts the 4-digit year from SYSDATE. TO_DATE is incorrect because it converts a string to a date, not the reverse. DECODE is not relevant for date formatting.

OPEN_FORM with the ACTIVATE and SESSION parameters opens the Departments form in a separate session while keeping the Employees form available. This allows clerks to enter and save the new department independently, then return to decide about the employee. CALL_FORM would replace the form entirely, and NO_ACTIVATE/NO_SESSION would not provide the needed independence.

BULK COLLECT retrieves entire result sets into collections in a single operation, eliminating the need for explicit loops or cursor fetches. This is a performance optimization that reduces context switching between the PL/SQL and SQL engines. Procedures and functions are program units, not data retrieval mechanisms.

Selection refers to filtering rows based on conditions. WHERE d.deptno = 10 selects only rows where deptno equals 10 - this is pure selection. WHERE d.deptno = e.deptno is joining (relating two tables). SELECT * and SELECT id_number... are projection operations (choosing columns), not selection.

I/O slaves are specialized processes that only perform write operations to data files. DBWn (Database Writer) processes perform both data gathering (identifying dirty buffers) and writing them to disk. The synchronous/asynchronous I/O distinction is incorrect - both can work with either type.

Subqueries allow dynamic query execution where the exact condition may not be known at design time - the inner query's results determine the outer query's behavior. ORDER BY and GROUP BY are for sorting and aggregation, not conditional logic. WHERE with OR only handles known conditions.

OSDBA is the operating system group that grants SYSDBA privileges when connecting to an Oracle database. Members of this group can perform database administration tasks. The other options (9iDBA, OSADM, OSOPER) are not valid Oracle operating system groups for SYSDBA access.

PARTITION BY in a partitioned outer join divides the result set rows based on the specified expression, creating distinct partitions for the join operation. It does not partition the underlying table or create equal segments - it's about organizing the output for the join, not physical data distribution.

After configuring connection parameters (service name, protocol, host, port), the Oracle Net Configuration Assistant's logical next step is testing whether the connection works. This validates the configuration before completing. The listener and net service name would have been configured earlier in the process.

Direct-path export bypasses the SQL statement processing layer, writing data directly to the export file, which is significantly faster than conventional path. Export cannot export tablespaces or data files directly (only schema objects). FULL export respects schema boundaries. Transportable tablespaces are faster than export/import for table transfer.

The LOG_ARCHIVE_START parameter in the initialization parameter file controls whether automatic archiving starts when the instance starts up. Setting it to TRUE enables this. Automatic archiving is not the default. LOG_ARCHIVE_MAX_PROCESSES controls the number of archiver processes, not whether archiving is enabled.

The query uses OR logic - it returns employees where EITHER the commission_pct equals 0.5 (50%) OR salary exceeds 23000. The parentheses in the description matter - it's not '50% OF salary greater than 23000' (which would be mathematically different), but two separate conditions joined by OR.

The UPDATE GLOBAL INDEXES clause ensures that dropping a partition does not invalidate global indexes. Instead of marking indexes UNUSABLE, Oracle asynchronously maintains them during the DDL operation. This allows concurrent DML operations to continue accessing global indexes without interruption, improving availability during partition maintenance.