Oracle Database Administration and SQL Practice Test

This statement is TRUE. In Oracle Database, dynamic performance views (V$ views) gather their runtime information from the control file and instance memory. The control file contains critical database metadata and status information that these views query to provide real-time performance statistics.

The error ORA-01034 'ORACLE not available' combined with ORA-07318 'sgadef.dbf file open error' and 'No such file or directory' definitively indicates the Oracle instance is shutdown. The sgadef.dbf file exists only when the instance is running (it's part of SGA - System Global Area). The error is not about segments or namespaces but the instance itself being unavailable.

In Oracle's local naming method, a service name provided in the connection request is mapped to a Connect Descriptor stored in the tnsnames.ora file. A Connect Descriptor contains the complete network address (protocol, host, port, service name) needed to establish the connection. Connect Identifier is the generic name, Connect String is the full syntax, and Connected Directory is not a standard Oracle term.

Procedural replication is specifically designed to execute the same stored procedures or transactions at multiple sites, making it ideal for batch processing scenarios where the same operations need to be performed across distributed databases. Unlike other replication methods, procedural replication replicates the execution of procedures rather than just copying data changes.

The query uses AND to combine two contradictory conditions: sal<2000 AND sal>9000. Since a value cannot simultaneously be less than 2000 AND greater than 9000, no rows will satisfy both conditions, resulting in an empty result set.

When importing data using Oracle's Import utility, the buffer size must be at least as large as the longest row in the table to prevent data truncation or errors. Since the longest row is 8096 bytes, the buffer should be set to at least 8096 bytes to accommodate it.

When CONFIGURE CONTROLFILE AUTOBACKUP ON is enabled, RMAN automatically creates a control file autobackup after every backup operation, regardless of whether the backup set already includes a control file backup. This provides an extra layer of protection for the control file, which is critical for database recovery.

The EDIT command in SQLPlus invokes the external text editor that has been linked to SQLPlus. This allows you to edit SQL statements or PL/SQL blocks in your preferred editor and then return to SQL*Plus to execute them.

A single Oracle listener process can listen for and handle connection requests for multiple databases. The listener uses service names or SID to identify which database a client wants to connect to and routes the connection accordingly.

When you create a variable using the DEFINE command in SQL*Plus, it is always assigned the CHAR datatype by default. DEFINE variables are substitution variables that store text strings and are replaced with their values before SQL statement execution.

Cursors in PL/SQL can accept parameters, making them more flexible and reusable. Parameterized cursors allow you to pass values at runtime, enabling the same cursor definition to be used with different data sets based on the parameters provided.

For storing Asian languages efficiently, AL16UTF16 (fixed 2-byte per character) is optimal because it uses consistent space regardless of the character, unlike UTF8 which uses 1-3 bytes variably. NCHAR data types with AL16UTF16 national character set saves space for Asian characters while maintaining consistent character manipulation performance. Changing to UTF8 would increase storage requirements for Asian characters (3 bytes each instead of 2).

USER_OBJECTS is a data dictionary view that displays all objects owned by the current user, including tables, views, indexes, procedures, and other database objects. It only shows objects where the user is the owner, unlike ALL_OBJECTS which shows objects accessible to the user (including ones owned by others with privileges).

Current value of the EMP_ID_SEQ sequence

In a SELECT statement with both WHERE and GROUP BY clauses, the WHERE clause must come before the GROUP BY clause. The correct order is: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY. WHERE filters rows before grouping, while GROUP BY aggregates the filtered rows.

In Oracle Shared Server architecture, client processes connect to dispatchers, which are responsible for managing client connections and routing requests to shared server processes. The dispatcher acts as an intermediary between the client and the shared server processes, enabling multiple clients to be served by fewer server processes.

RMAN maintains metadata about which archived redo logs have been backed up in the target database's control file and/or the recovery catalog. When you issue a BACKUP ARCHIVELOG command, RMAN queries this metadata to identify which archived logs need backing up and automatically selects them without requiring manual specification.

The LIST command displays the current contents of the SQLPlus buffer. This is useful when you need to review or modify a SQL statement before executing it. Other options like CONTENTS, BUFFER, and CURRENT are not valid SQLPlus commands.

Oracle allocates SGA memory in units called granules. When the total SGA size exceeds 128 MB, the granule size becomes 16 MB; for SGAs 128 MB or smaller, Oracle uses 4 MB granules for better memory management efficiency. This tiered approach optimizes memory allocation overhead.

TRUNC function on a date truncates it to the specified precision. Using 'YEAR' as the format mask truncates the date to January 1st of that year, discarding the month and day information. Option D correctly demonstrates TRUNC applied to a date with proper syntax to round down to the first day of the year.

TRUNCATE is a DDL command that deallocates data pages without generating redo or rollback information, making it much faster than DELETE for large tables. DROP removes the entire table structure, CASCADE is used with DROP for dependencies, and DELETE is a DML command that generates extensive rollback and redo logs.

DBA_INDEXES contains all index information including index names, types (NORMAL, BITMAP, FUNCTION-BASED, etc.), status, and other properties. DBA_IND_COLUMNS shows which columns comprise each index but doesn't contain the index type information. DBA_USERS contains user account information, not index metadata.