databases Online Quiz - 71

The INVALID_FILEHANDLE exception is raised by UTL_FILE procedures like FCLOSE and GET_LINE when they receive an invalid or uninitialized file handle. Conversely, the IS_OPEN function evaluates whether a handle is open and returns a boolean value instead of raising this exception, making the combination 1,2 correct.

INVALID_MAXLINE_SIZE is a valid UTL_FILE exception raised when the MAX_LINESIZE parameter in FOPEN or FOPEN_NCHAR exceeds implementation limits. Other valid exceptions include INVALID_PATH, INVALID_MODE, INVALID_FILEHANDLE, and WRITE_ERROR, but INVALID_OFFSET, VALUE_ERROR, and ACCESS_DENIED are not standard UTL_FILE exceptions.

UTL_FILE can read files on Windows systems, but only if the Oracle database server is running on Windows and the directory is properly configured in the DIRECTORY object and utl_file_dir parameter. The question likely tests whether you understand UTL_FILE operates server-side, not client-side, so it depends on the server OS, not the client environment.

PUT_LINE in UTL_FILE package writes text to a file and automatically appends a line terminator (newline character) after each call. PUT writes without any terminator, while PUTF is for formatted output without automatic line termination. NEW_LINE is not a valid UTL_FILE procedure.

UTL_FILE_DIR is the initialization parameter that specifies which directories can be accessed by the UTL_FILE package. This parameter must be set in the database initialization file (init.ora or spfile) to allow file operations. The other options are not valid Oracle initialization parameters.

The default max_linesize parameter in UTL_FILE.FOPEN is 1024 bytes. This parameter controls the maximum line length that can be written to or read from a file. It can be explicitly set up to 32767 bytes if needed for longer lines.

In a RAC environment, proper SQL logging requires instance number, SID, and serial# to uniquely identify the session and instance generating the log. RAC has multiple instances, so all three identifiers are necessary to trace logs to the exact source. Missing any identifier makes it impossible to determine which instance or session generated a particular log entry.

Professional code documentation should include revision history with dates, the reason for each change, author identification, and reference to change requests (CR). This comprehensive approach ensures maintainability and auditability. Missing any of these elements makes it difficult to track changes or understand the context of modifications.

EIP database supports multiple scheduling tools: CONTROL M (external enterprise scheduler), DBMS_JOB (legacy Oracle package), and DBMS_SCHEDULER (modern Oracle scheduler). The choice depends on specific requirements, but all three are valid options for scheduling jobs in the EIP environment.

Simple SQL performance can be optimized using multiple techniques: dynamic parallelism (parallel execution), hints (directives to optimizer), and stored outlines (preserving execution plans). The effectiveness depends on the specific SQL statement and environment. All three methods are valid optimization strategies.

A complete refresh of a materialized view deletes all existing rows before inserting the new dataset. This differs from fast refresh (incremental) which only applies changes. The DELETE operation ensures the mview contains exactly the data from the refresh query, with no stale rows remaining.

Local indexes are preferred on partitioned tables because they are automatically maintained with partition operations and provide better partition pruning. Global indexes span all partitions and require more maintenance during partition operations like DROP or TRUNCATE. Local indexes align with the table's partitioning strategy.

To perform a partition exchange, the exchange table must have matching indexes and statistics built before the exchange. This allows Oracle to swap metadata instantly without needing to rebuild indexes on the destination partitioned table. Building them after or during the exchange is not feasible because the structures must align beforehand.

Bitmap indexes are ideal for columns with low cardinality (non-unique values) because they store bitmaps efficiently for repetitive values. B-tree indexes work better for unique or high-cardinality columns. A composite unique index would require unique values, and cluster index is not the standard term for non-unique column indexing.

Packages provide encapsulation by bundling related procedures, functions, variables, and cursors together, with public/private specifications controlling access. Procedures and functions are individual program units, but only packages offer true encapsulation through the package specification and body structure. Packages also support session-specific state persistence.

Working tables should be truncated at the beginning of the next run, not at the end of the current run. This allows verification that the previous run completed successfully before clearing data. Truncating at the end risks losing the working data if an error occurs before verification. The beginning of the next run ensures a clean slate while preserving the previous run's data for audit purposes.

Commenting out code preserves it for reference while preventing execution, which is the standard practice for script maintenance. Deleting code permanently loses the logic, which cannot be recovered if needed later. This approach allows rollback without version control.

Using the partition key in filter conditions enables partition pruning, where the optimizer automatically accesses only relevant partitions. Explicit partition references make code rigid and break when partitions change. The query optimizer automatically selects partitions based on the partition key in WHERE clauses.