Oracle Practice Test - 20

In Oracle, the RENAME command performs an automatic commit because it is a DDL (Data Definition Language) operation. All DDL commands in Oracle have implicit commit behavior. Other options listed are either DML (INSERT, DELETE) or session commands (CONNECT, SELECT) which do not auto-commit.

The APPEND hint in Oracle INSERT statements enables Direct-Load insert, which bypasses the buffer cache and writes data directly to data files. This method is faster than conventional path inserts and is used for bulk data loading operations. The '+' prefix indicates a hint.

CURSOR_SPACE_FOR_TIME parameter set to TRUE tells Oracle to keep cursors in memory even when not in use, trading memory for performance. Before enabling this, you must confirm that RELOADS in V$LIBRARYCACHE is consistently zero - meaning SQL statements aren't being reloaded, indicating sufficient shared pool memory.

If-Then-Else is a conditional control structure in PL/SQL that allows unconditional approach to different parts of code based on condition evaluation. The IF statement evaluates a condition and executes different code blocks accordingly, providing structured decision-making capability.

SYSDATE is an Oracle SQL function that returns the current date and time from the database server's operating system. TO_DATE converts string to date, DATE is a datatype not a function, and SYSTIME does not exist in Oracle. SYSDATE is the correct function to view current time.

To grant remote DBA access in Oracle, you must grant SYSDBA privilege to the user. The GRANT SYSDBA TO ALLEN command adds Allen to the password file with SYSDBA privileges, allowing remote database startup and administration. Option A is incorrect because DBA is a role, not a privileged connection. Option D shows orapwd syntax for creating password files, not granting privileges.

The 'Checkpoint Not Complete' message occurs when LGWR cannot write to redo log files because a checkpoint hasn't completed. This happens when redo logs are too small or too few. Solutions include: adding more redo log groups (B) so LGWR has more available logs, and increasing size of online redo log files (D) to provide more space between checkpoints. Deleting archived logs (A) doesn't help and increasing archived log size (C) is irrelevant.

The DESCRIBE command (or DESC for short) is the correct SQL*Plus command to display the structure of a table, showing column names, data types, nullability, and other metadata. Option C adds an unnecessary STRUCTURE keyword, and options A and D use incorrect syntax.

In PL/SQL block structure, user-defined exceptions are trapped and handled in the EXCEPTION section. The DECLARE section is for declarations, the EXECUTE section (between BEGIN and END) contains executable logic, and there is no 'Header' section in standard PL/SQL.

The statement is incorrect. Referential integrity involves FOREIGN KEYs referencing PRIMARY KEYs or UNIQUE constraints, not super keys. A super key is a set of columns that uniquely identifies rows within a single table, not a parent-child relationship concept. The statement confuses different database concepts.

The FAST_START_PARALLEL_ROLLBACK parameter in Oracle can only be set to LOW or HIGH (values are case-insensitive). Setting it to a numeric value like 5 is invalid syntax, causing the parameter to be ignored. Option A is incorrect because rollback processes use the LOW/HIGH logic, not specific process counts. Option B incorrectly states a maximum of four processes. Option D is wrong because RECOVERY_PARALLELISM is a different, unrelated parameter.

Oracle password files have a fixed size determined at creation time. You cannot alter, resize, or edit the file after creation (options A and B are invalid operations). The file does not auto-grow (option C is wrong). To change the entry limit, you must recreate the password file with ORAPWD using a different ENTRIES parameter, then re-add all users (option D is correct, though cumbersome).

DBA_USERS contains the ACCOUNT_STATUS column (shows expired/open), DEFAULT_TABLESPACE column, and a PROFILE column. All three required pieces of information are in this single view. DBA_PROFILES is not needed because the profile name (not profile details) is already in DBA_USERS. DBA_TABLESPaces is unnecessary for this query. DBA_TS_QUOTAs shows tablespace quotas, not required information.

In a RIGHT OUTER JOIN, all rows from the right table (B) are returned, plus matching rows from the left table (A). Non-matching rows from A show NULL. A LEFT OUTER JOIN would return all from A, not B. An INNER JOIN only returns matching rows from both. A CROSS JOIN returns the Cartesian product (all combinations).

The control file is a core binary file that is part of the Oracle database, containing critical metadata like database name, data file locations, checkpoint information, and redo log thread info. The database CANNOT open without at least one control file. Password files, parameter files, and archived log files are important but are not technically 'part of the database' in the same architectural sense.