Oracle 9i and SQL Database Quiz

Views cannot be modified using ALTER. To change a view definition, you must DROP and recreate it. In contrast, ALTER TABLESPACE can change attributes like logging, ALTER SEQUENCE can restart or change increment, and ALTER INDEX can modify certain index properties.

True. The LOAD utility rebuilds indexes after loading data, especially when using REPLACE method which deletes existing data. For INSERT method or with certain options, indexes may be incrementally updated, but LOAD always ensures index consistency by rebuilding or updating them.

ABS is a scalar function that operates on individual values, while AVG, SUM, and STDDEV are aggregate functions that operate on sets of values and require GROUP BY or appear in HAVING clauses. Scalar functions can be used anywhere expressions are allowed.

The DB2 Directory contains Database Descriptors (DBDs), which are internal control structures describing database physical structures. The DB2 Catalog stores metadata about database objects, the Optimizer uses statistics for access path selection, and the Data Manager manages actual data access.

The DB2/SQL EXPLAIN statement inserts detailed information about the access path of the query being explained into a table named PLAN_TABLE. DSN_STATEMNT_TABLE contains cost estimates, while SYSPLAN and SYSPACKAGE are catalog tables storing plan and package metadata rather than explain output.

In Oracle 9i database, the OSDBA operating system group is mapped to the SYSDBA database privilege, allowing users within this group to authenticate via the operating system. OSOPER maps to the SYSOPER privilege. Other options like 9iDBA or OSADM are not standard Oracle-predefined OS groups for database administration privileges.

The SYSOPER administrative privilege in Oracle databases permits basic operations like STARTUP, SHUTDOWN, and MOUNT/OPEN/CLOSE of a database. However, database creation requires the more powerful SYSDBA privilege.

Oracle's recommended approach requires backing up the fully functional database before beginning any tuning activities. This ensures you have a recovery point if tuning changes cause issues. Installing software, planning, and implementing design are prerequisites but must complete before the database is fully functional and ready for backup.

Evaluating the database server hardware is Oracle's first recommended task before any software installation or database design. Hardware assessment ensures the server meets requirements for memory, CPU, storage, and I/O capacity. Software installation (A), planning (D), and implementation (C) all happen after hardware evaluation.

In Oracle databases, the default administrative user account 'SYS' is traditionally initialized with the default password 'CHANGE_ON_INSTALL'. The 'SYSTEM' account defaults to 'MANAGER', and 'SYSOPER' is a system privilege rather than a base user account.

The TZ environment variable sets the timezone and directly affects how the date command displays time. It determines the time zone used for formatting dates. Options B and C (TMP, TMPDIR) are for temporary file directories. Option D (SHLIB_PATH) is for shared library search paths.

The oraenv script provides a central mechanism for switching between different Oracle database instances by updating environment variables like ORACLE_SID, ORACLE_HOME, and PATH for all user accounts. Option C correctly describes this central update function. Options A and B mention scanning functions which don't apply. Option D describes database switching, which is the result, not the function.

BACKGROUND_DUMP_DEST specifies a directory path where Oracle writes background process trace files and alert logs. Options A (DB_CACHE_SIZE), B (DB_FILES), and D (DB_BLOCK_SIZE) are all numeric parameters - sizes, counts, or block sizes - not directory paths. Only BACKGROUND_DUMP_DEST accepts a directory name as its value.

DB_BLOCK_SIZE is a fundamental storage parameter that can only be set when a database is first created. It determines the size of data blocks and cannot be changed by simply modifying parameters or restarting - the entire database must be recreated with the new block size.

In Oracle 9i, the question mark (?) is used to reference or represent the ORACLE_HOME environment variable value in various contexts. This is part of Oracle's substitution syntax for environment variables.

In SQL*Plus and Oracle SQL Developer, the ampersand character (&) is the default prefix used to denote substitution variables. When the SQL parser encounters an ampersand, it prompts the user for a value, making this the correct option while other symbols are incorrect.