Q: SQLPlus Identifier Case — Defaults and Behavior When working in SQLPlus, Oracle commands, column names, table names, and other identifiers are typically treated how with respect to case (ignoring quoted identifiers)?

Introduction:Understanding how Oracle and SQLPlus handle identifier case helps prevent confusing errors when writing scripts and querying the data dictionary. By default, unquoted identifiers are normalized by the database, which affects how names are stored and matched. Given Data / Assumptions: SQLPlus passes SQL statements to Oracle for parsing. Unquoted identifiers are folded to uppercase by Oracle and compared case insensitively. Quoted identifiers preserve case but require consistent quoting to reference. Concept / Approach:When you create an object without quotes (for example, CREATE TABLE myTable ...), Oracle stores the name internally in uppercase (MYTABLE). Subsequent references without quotes will match regardless of the case you type in SQLPlus. Case sensitivity appears only when you intentionally use quoted identifiers (for example, "MyTable"), which then must be referenced exactly, including case and quotes. Therefore, by default and in typical practice, identifiers behave case insensitively in SQLPlus. Step-by-Step Solution:1) Consider an unquoted identifier: create table mytab ...2) Oracle stores MYTAB; SELECT * FROM MyTab succeeds because matching is case insensitive for unquoted identifiers.3) If created as "MyTab", you must always use SELECT * FROM "MyTab".4) Conclude that the default behavior is effectively case insensitive. Verification / Alternative check:Data dictionary views (USER_TABLES) show object names in uppercase for unquoted definitions, supporting the folding behavior. Why Other Options Are Wrong: Case sensitive: Only true with quoted identifiers. Always lower/upper: Users can type any case; Oracle folds unquoted names. Mixed sensitivity by object type: Not accurate; rules apply uniformly to unquoted identifiers. Common Pitfalls:Creating quoted identifiers and later forgetting to quote them exactly, leading to “table or view does not exist” errors. Final Answer:They are case insensitive.

Q: SQLPlus Editing — Invoking an External Text Editor You have linked SQLPlus to an external text editor for editing SQL statements. Which SQLPlus command do you use to open the editor from within SQLPlus?

Introduction:SQLPlus supports external editing so that you can modify the current buffer or a script file using your preferred text editor. Knowing the correct command improves productivity and integrates your workflow with version-controlled scripts. Given Data / Assumptions: An external editor (for example, Notepad, vi, or VS Code) has been configured with SQLPlus. You want to invoke the editor from inside the SQLPlus session. SQLPlus maintains a buffer that holds the most recently entered or retrieved command(s). Concept / Approach:The SQLPlus EDIT command launches the configured external editor. If you supply a filename, SQLPlus opens that file; otherwise, it opens the contents of the SQL buffer in a temporary file. After saving and closing the editor, you can use @ or START to run saved scripts, or LIST to view the buffer. Step-by-Step Solution:1) Type EDIT to open the buffer in the external editor, or EDIT file.sql to open a specific file.2) Make changes in the editor and save the file.3) Return to SQLPlus; use LIST to review buffer content or @file.sql to execute.4) Confirm that EDIT is the correct command (not OPEN/SHOW/ALTER). Verification / Alternative check:SQLPlus help (HELP EDIT) documents the behavior and optional filename argument. Why Other Options Are Wrong: OPEN/SHOW/ALTER/WRITE: Not valid SQL*Plus commands for launching the editor; SHOW displays settings, WRITE is not the standard editor launch, ALTER is SQL DDL. Common Pitfalls:Forgetting to set the editor with DEFINE _EDITOR or the environment variable; EDIT then launches the default editor for the platform. Final Answer:EDIT [fileName]

Q: SQLPlus Buffer — Viewing the Current Statement Within SQLPlus, which command displays the contents of the SQLPlus buffer (the current SQL statement or statements)?

Introduction:SQLPlus keeps a working copy of your most recent SQL or PL/SQL input in an internal buffer. Quickly viewing this buffer avoids retyping and helps with iterative development. This question checks your knowledge of the command used to display the buffer content. Given Data / Assumptions: You are working at the SQLPlus prompt. Some SQL has already been entered or fetched via GET/EDIT. You want to display the buffered text. Concept / Approach:The LIST command (shorthand L) prints the current contents of the SQLPlus buffer to the screen with line numbers. From there you can use APPEND, CHANGE, or EDIT to modify the text, or RUN/; to execute it. LIST is distinct from SHOW, which reports settings (for example, SHOW ALL), and from DESCRIBE (DESC), which inspects object metadata. Step-by-Step Solution:1) At the prompt, type LIST to display the buffered SQL.2) Confirm that the statement shown matches what you intend to execute.3) Make edits if necessary using EDIT or in-line editing commands.4) Execute with RUN or a terminating semicolon. Verification / Alternative check:HELP LIST in SQLPlus documents the command and its options, including listing specific line ranges. Why Other Options Are Wrong: CONTENTS/BUFFER/CURRENT/SHOWSQL: These are not the standard SQLPlus commands for printing the buffer. Common Pitfalls:Confusing LIST with SHOW; SHOW does not print the SQL buffer. Also, remember that the buffer persists only within the current SQL*Plus session. Final Answer:LIST

Q: Oracle Backup Types — Consistent vs. Inconsistent In Oracle backup terminology, which backup type ensures that all uncommitted changes have been removed from the datafiles (that is, the database is in a transaction-consistent state at the backup poi…

Introduction:Backups can be taken while the database is open or closed. The resulting recovery steps depend on whether the backup captures datafiles in a transaction-consistent state. This question asks you to identify the backup type where uncommitted changes have been cleared from the datafiles. Given Data / Assumptions: A consistent backup is taken when the database is cleanly closed or quiesced. An inconsistent backup is taken while the database is open and requires redo to recover consistency. Terminology distinguishes consistency from scope (full vs. incremental). Concept / Approach:In a consistent backup, the datafiles on disk reflect only committed transactions as of the checkpoint, so recovery does not need to apply redo to make blocks consistent. In an inconsistent backup, redo application (media recovery) is necessary to roll forward uncommitted and committed changes to a consistent point. Therefore, the type that has already removed uncommitted changes is the consistent backup. Step-by-Step Solution:1) Map “uncommitted changes removed” to transaction-consistent datafiles.2) Recognize this corresponds to a consistent backup (typically taken with the database closed).3) Select “Consistent backup.” Verification / Alternative check:Oracle backup and recovery documentation explains that only inconsistent backups require redo application to reach a consistent point-in-time. Why Other Options Are Wrong: Full / Differential / Cumulative incremental: These describe scope, not consistency. Inconsistent backup: Requires redo to become consistent; the opposite of the stated condition. Common Pitfalls:Confusing “full” with “consistent.” You can have a full backup that is either consistent or inconsistent depending on database state at backup time. Final Answer:Consistent backup

Q: SQLPlus Scripts — Default File Extension When saving SQL scripts for use with Oracle SQLPlus, what is the default and conventional filename extension that SQLPlus expects?

Introduction:SQLPlus is Oracle’s classic command-line tool for running SQL and PL/SQL scripts. Consistent file naming conventions make automation and execution straightforward. This question checks your knowledge of the conventional default extension used for SQLPlus script files. Given Data / Assumptions: You are saving a SQL script to be executed with START or @ in SQLPlus. Extensions help users and tools recognize file types quickly. The goal is to choose the standard extension used across examples and documentation. Concept / Approach:SQLPlus runs scripts with START file or @file. Although you can execute files without an extension by specifying the exact name, the convention in Oracle documentation and practice is to save SQL command files with the .sql extension. Other extensions (.pls for PL/SQL packages in some shops, .ctl for SQLLoader control files, .ora for network/parameter files) serve different purposes and are not the default for SQLPlus scripts. Step-by-Step Solution:1) Identify the tool and use case: running SQL scripts in SQLPlus.2) Recall standard examples using @script.sql or START script.sql.3) Select .sql as the expected extension. Verification / Alternative check:Oracle tutorials and guides consistently show .sql for SQLPlus scripts, confirming the convention. Why Other Options Are Wrong: .txt: Plain text, not the standard for executable SQL scripts. .pls: Sometimes used informally for PL/SQL sources, but not the SQLPlus default. .ora: Oracle Net and parameter files (tnsnames.ora, sqlnet.ora, init*.ora). .ctl: SQL*Loader control files. Common Pitfalls:Naming scripts inconsistently makes automation harder; standardize on .sql for clarity and tooling support. Final Answer:.sql

Q: In Oracle Database, which value-prefixes can be referenced inside row-level triggers to access the old and new row values?

Introduction / Context:Oracle Database supports row-level triggers that fire for each affected row during INSERT, UPDATE, or DELETE operations. Inside these triggers, Oracle exposes special pseudo-records to read or assign column values. Knowing which prefixes are available is essential for correct trigger logic. Given Data / Assumptions: The question concerns Oracle row-level trigger syntax. Available prefixes may include :new and :old to reference column values. We assume standard Oracle PL/SQL trigger behavior. Concept / Approach::new refers to the new column values (after change) and :old refers to the original column values (before change). Their availability depends on the DML operation: INSERT provides :new, DELETE provides :old, and UPDATE provides both. Step-by-Step Solution: For INSERT triggers, :new is populated; :old is not meaningful.For DELETE triggers, :old is populated; :new is not meaningful.For UPDATE triggers, both :new and :old are available for comparison and auditing. Verification / Alternative check:Oracle PL/SQL documentation consistently demonstrates :new and :old usage within row-level triggers for all three DML events. Why Other Options Are Wrong::new only and :old only are each incomplete because availability depends on the DML statement. Neither :new nor :old is false since Oracle exposes these pseudo-records in row-level triggers. Common Pitfalls:Trying to assign to :old in UPDATE/INSERT triggers (not allowed). Also, attempting to reference :new in DELETE triggers or :old in INSERT triggers will fail. Final Answer:Both :new and :old

Q: In Oracle SQLPlus, after compiling a stored procedure that has errors, which statement correctly describes how to view those compilation errors?

Introduction / Context:When compiling PL/SQL objects (procedures, functions, packages) in SQLPlus, syntax or semantic errors are stored in the user_errors/dba_errors views. Developers must explicitly request a listing to diagnose issues. Given Data / Assumptions: The environment is SQLPlus (or tools that mimic its commands). A stored procedure was compiled and has errors. We want the correct way to display those errors. Concept / Approach:SQLPlus provides the command SHOW ERRORS, which displays the latest compilation errors for the most recently created or altered PL/SQL object, including line/position references relative to the database-stored source. Step-by-Step Solution: Compile the object with CREATE OR REPLACE PROCEDURE ...;If errors occur, run: SHOW ERRORS;Optionally, query USER_ERRORS for detailed diagnostics and filtering. Verification / Alternative check:Using SELECT * FROM USER_ERRORS WHERE NAME = 'PROC_NAME' ORDER BY SEQUENCE; confirms the same details that SHOW ERRORS surfaces. Why Other Options Are Wrong:Automatic display (option b) is not the SQLPlus default; you must request errors. Line numbers matching editor (option a) often differ due to stored text formatting. 'NO ERRORS.' (option d) is not the normal success message; SQLPlus typically shows 'Procedure created.' when compilation succeeds. Common Pitfalls:Confusing client editor line numbers with database-stored line numbers; forgetting to use SHOW ERRORS after CREATE OR REPLACE. Final Answer:To see the errors, enter SHOW ERRORS in SQLPlus.

Q: Which of the following is NOT a trigger type supported by Oracle Database?

Introduction / Context:Triggers in Oracle can fire before or after DML events and, for views, Oracle supports INSTEAD OF triggers. Recognizing valid trigger timing keywords is fundamental for database programming. Given Data / Assumptions: Oracle supports various trigger timings and event scopes. Choices include BEFORE, AFTER, INSTEAD OF, and a distractor. Concept / Approach:Oracle valid timing keywords are BEFORE and AFTER for tables, and INSTEAD OF for views. There is no DURING timing in Oracle trigger syntax. Step-by-Step Solution: List valid keywords: BEFORE, AFTER, INSTEAD OF.Compare with options. DURING does not exist.Select DURING as the incorrect (unsupported) option. Verification / Alternative check:Oracle SQL Language Reference documents only BEFORE, AFTER, and INSTEAD OF timings for triggers. Why Other Options Are Wrong:BEFORE is a standard trigger timing. AFTER is also standard. INSTEAD OF is used on views to substitute DML behavior. Common Pitfalls:Assuming a natural-language timing like DURING exists; Oracle requires precise keywords. Final Answer:DURING

Question 1

After a table has been created, which SQL command is used to modify its structure (for example, add or drop columns and constraints)?

Accepted Answer

Introduction / Context:SQL distinguishes between changing data and changing structure. Data Manipulation Language (DML) statements edit row contents, while Data Definition Language (DDL) statements create or alter schema objects. This question asks which command changes an existing table's structure. Given Data / Assumptions: The table already exists. Required changes include adding or dropping columns, indexes, or constraints. We are looking for the standard DDL verb. Concept / Approach: ALTER TABLE is the portable DDL statement used across major SQL databases to modify the definition of an existing table. UPDATE modifies data, not structure. MODIFY TABLE and CHANGE TABLE are not standard SQL commands (though some vendors support ALTER TABLE ... MODIFY COLUMN as a clause). Step-by-Step Solution: 1) Choose ALTER TABLE to change schema elements of an existing table.2) Provide specific subclauses (for example, ADD COLUMN, DROP COLUMN, ADD CONSTRAINT).3) Validate in a non-production environment before applying to production. Verification / Alternative check: Consult vendor documentation; ALTER TABLE is universally recognized for structural changes, while UPDATE affects rows and should not be used for DDL. Why Other Options Are Wrong: UPDATE TABLE: DML for row values, not structure. MODIFY TABLE / CHANGE TABLE: not standard SQL verbs; vendors use ALTER TABLE with MODIFY or CHANGE as subclauses, not as the main command. Common Pitfalls: Attempting to use non-portable syntax across engines; forgetting to consider locking and online options during ALTER operations on large tables. Final Answer: ALTER TABLE [TableName].

Question 2

Oracle Backup & Recovery — Identifying Files That Contain User and System Data In Oracle Database administration, which backup-and-recovery file type actually stores persistent user and system data (that is, the table and index segments that make up…

Accepted Answer

Introduction:Oracle Database separates physical files by responsibility to improve performance, recoverability, and manageability. Knowing which file actually holds the persistent contents of tables, indexes, and other segments is essential for backup and recovery planning. This question focuses on distinguishing datafiles from control files and redo logs in a clear, exam-ready manner. Given Data / Assumptions: The Oracle database instance uses distinct file types: datafiles, control files, and redo log files (online and archived). Backups may capture these files in different ways (RMAN, user-managed). We are asked to identify which file physically stores user and system data segments. Concept / Approach:Datafiles are the physical containers that store database blocks for segments such as tables, indexes, materialized views, and LOBs. Control files store critical metadata (database structure, checkpoints, SCNs, and archived log history). Redo log files (online and archived) store change records used for media recovery and instance recovery; they do not store the data segments themselves. Therefore, the file that contains user and system data is the datafile. Step-by-Step Solution:1) List file roles: datafiles = segments; control files = metadata; redo = change records.2) Map the question phrase “contains user and system data” to the file holding table and index blocks.3) Conclude that this is the datafile. Verification / Alternative check:RMAN backups of datafiles are required to restore tablespaces and objects. During recovery, redo is applied to datafiles to roll forward changes; this dependency confirms where the actual data resides. Why Other Options Are Wrong: Control file: Stores structural metadata, not table/index blocks. Online/archived redo logs: Store change vectors for recovery, not persistent segment data. Password file: Governs SYSDBA authentication, unrelated to segment storage. Common Pitfalls:Confusing redo logs (which are essential for recovery) with the primary storage of table data. Redo replays changes; it does not hold base segment pages. Final Answer:Datafile

Question 3

Oracle Sequences — Identify the Statement That Is NOT True Which of the following statements about Oracle sequences is NOT correct? Choose the single best answer based on how sequences work in production databases.

Accepted Answer

Introduction:Sequences are lightweight number generators frequently used to populate surrogate keys in Oracle. Understanding what sequences do and do not guarantee is essential for schema design, data integrity, and troubleshooting gaps in identifiers. This question targets a common misconception about “guarantees.” Given Data / Assumptions: Sequences generate numbers independently of transaction commit/rollback outcomes. Applications often use sequences for primary key values. Caching and rollbacks can lead to gaps in the generated series. Concept / Approach:Sequences guarantee uniqueness at the generator level (no two calls to NEXTVAL return the same number). However, sequences do not guarantee “valid” surrogate keys in the broader sense of business correctness, continuity, or absence of gaps. If a transaction rolls back after consuming a number, that value is lost to the sequence stream; caching can also skip numbers during instance restarts. Therefore, any claim that sequences “guarantee valid surrogate key values” is not correct, because “valid” might imply additional constraints (no gaps, meaningfulness, or referential correctness) that are outside the scope of the sequence mechanism. Step-by-Step Solution:1) Evaluate uniqueness: sequences provide unique numbers per call.2) Evaluate validity: business validity depends on constraints and application logic, not the sequence alone.3) Consider gaps: rollbacks and cache behavior introduce gaps, which are acceptable but contradict “validity” if interpreted as “gapless.”4) Conclude that the “guarantee valid” statement is not true. Verification / Alternative check:Oracle documentation notes that sequences are independent objects; they do not guarantee gapless series and are not transactional. This confirms that “valid surrogate key values” is an overreach. Why Other Options Are Wrong: A: True by definition of sequences. B: True; sequences are commonly used for surrogate keys. C: True; NEXTVAL and CURRVAL are standard sequence constructs. E: True; gaps can occur and are expected. Common Pitfalls:Expecting sequences to enforce business integrity. Use constraints (PRIMARY KEY, FOREIGN KEY) and application validations for that purpose. Final Answer:Sequences guarantee valid surrogate key values.

Question 4

Oracle Database Creation — Recommended Easiest Method Among the three common approaches to creating an Oracle database, which method is considered the easiest and most widely recommended for administrators?

Accepted Answer

Introduction:Creating a new Oracle database involves configuring initialization parameters, storage structures, and options. Oracle offers multiple ways to do this, from fully guided tools to manual scripting. This question checks whether you know the easiest and most recommended method for most environments. Given Data / Assumptions: Administrators can create databases using DBCA, Oracle-provided scripts, or fully manual SQL. The goal is to minimize errors and standardize configuration. Typical deployments value repeatability and correctness under time pressure. Concept / Approach:DBCA (Database Configuration Assistant) is Oracle’s guided, interactive tool that configures initialization parameters, storage, options, and sample schemas. It reduces manual steps, avoids syntax errors, and documents choices. Scripts and CREATE DATABASE offer finer control but require deeper expertise and are more error-prone if used by less experienced staff. Therefore, DBCA is widely recommended as the easiest method for most cases. Step-by-Step Solution:1) Compare methods by complexity and risk (DBCA < scripts < manual SQL).2) Consider the need for consistent, repeatable environments.3) Conclude that DBCA is the easiest and recommended method. Verification / Alternative check:Oracle installation and administration guides consistently present DBCA as the primary tool for database creation in standard deployments. Why Other Options Are Wrong: Oracle scripts / manual SQL: Valid but more complex and error-prone. None of the above / third-party GUI: Incorrect or nonstandard compared to Oracle’s own tooling. Common Pitfalls:Over-customizing a new database via manual steps before establishing a stable baseline with DBCA; start simple, then tune. Final Answer:Using the Oracle Database Configuration Assistant (DBCA).

Question 5

Oracle Indexes — Identify the Statement That Is NOT True Which of the following statements about Oracle indexes is NOT correct? Select the single best answer.

Accepted Answer

Introduction:Indexes are critical performance structures in Oracle, allowing the database to locate rows efficiently. However, not every popular statement about indexes is accurate. This question asks you to spot the incorrect statement regarding how indexes are created and used. Given Data / Assumptions: Oracle supports B-tree and bitmap indexes, among others. Unique constraints are typically enforced using underlying unique indexes. Administrators and developers must know the proper DDL commands for index management. Concept / Approach:In Oracle, the direct DDL to create an index is CREATE INDEX (or CREATE BITMAP INDEX, etc.). While ALTER TABLE ADD CONSTRAINT UNIQUE may implicitly create a unique index, indexes themselves are not created by ALTER TABLE as a standalone index-creation command. Therefore, the statement “Indexes are created with the ALTER TABLE command” is not correct. The other statements reflect common best practices or capabilities: enforcing uniqueness, improving retrieval speed, indexing columns used in equality predicates, and the use of bitmap indexes on low-cardinality columns in analytic systems. Step-by-Step Solution:1) Recall the correct DDL for indexes: CREATE INDEX ...2) Note that ALTER TABLE is for table alterations and constraints; it may indirectly create an index via a UNIQUE/PRIMARY KEY constraint.3) Validate that performance and uniqueness statements are accurate.4) Select the incorrect statement: “Indexes are created with the ALTER TABLE command.” Verification / Alternative check:Oracle SQL Language Reference shows CREATE INDEX syntax; ALTER TABLE is not the canonical method for creating indexes except indirectly through constraints. Why Other Options Are Wrong: A, B, C, E: Accurately reflect index roles and appropriate use cases. Common Pitfalls:Assuming a constraint and an index are interchangeable. Constraints enforce rules; indexes are physical structures that may support constraints. Final Answer:Indexes are created with the ALTER TABLE command.

Question 6

SQLPlus Identifier Case — Defaults and Behavior When working in SQLPlus, Oracle commands, column names, table names, and other identifiers are typically treated how with respect to case (ignoring quoted identifiers)?

Accepted Answer

Introduction:Understanding how Oracle and SQLPlus handle identifier case helps prevent confusing errors when writing scripts and querying the data dictionary. By default, unquoted identifiers are normalized by the database, which affects how names are stored and matched. Given Data / Assumptions: SQLPlus passes SQL statements to Oracle for parsing. Unquoted identifiers are folded to uppercase by Oracle and compared case insensitively. Quoted identifiers preserve case but require consistent quoting to reference. Concept / Approach:When you create an object without quotes (for example, CREATE TABLE myTable ...), Oracle stores the name internally in uppercase (MYTABLE). Subsequent references without quotes will match regardless of the case you type in SQLPlus. Case sensitivity appears only when you intentionally use quoted identifiers (for example, "MyTable"), which then must be referenced exactly, including case and quotes. Therefore, by default and in typical practice, identifiers behave case insensitively in SQLPlus. Step-by-Step Solution:1) Consider an unquoted identifier: create table mytab ...2) Oracle stores MYTAB; SELECT * FROM MyTab succeeds because matching is case insensitive for unquoted identifiers.3) If created as "MyTab", you must always use SELECT * FROM "MyTab".4) Conclude that the default behavior is effectively case insensitive. Verification / Alternative check:Data dictionary views (USER_TABLES) show object names in uppercase for unquoted definitions, supporting the folding behavior. Why Other Options Are Wrong: Case sensitive: Only true with quoted identifiers. Always lower/upper: Users can type any case; Oracle folds unquoted names. Mixed sensitivity by object type: Not accurate; rules apply uniformly to unquoted identifiers. Common Pitfalls:Creating quoted identifiers and later forgetting to quote them exactly, leading to “table or view does not exist” errors. Final Answer:They are case insensitive.

Question 7

SQLPlus Editing — Invoking an External Text Editor You have linked SQLPlus to an external text editor for editing SQL statements. Which SQLPlus command do you use to open the editor from within SQLPlus?

Accepted Answer

Introduction:SQLPlus supports external editing so that you can modify the current buffer or a script file using your preferred text editor. Knowing the correct command improves productivity and integrates your workflow with version-controlled scripts. Given Data / Assumptions: An external editor (for example, Notepad, vi, or VS Code) has been configured with SQLPlus. You want to invoke the editor from inside the SQLPlus session. SQLPlus maintains a buffer that holds the most recently entered or retrieved command(s). Concept / Approach:The SQLPlus EDIT command launches the configured external editor. If you supply a filename, SQLPlus opens that file; otherwise, it opens the contents of the SQL buffer in a temporary file. After saving and closing the editor, you can use @ or START to run saved scripts, or LIST to view the buffer. Step-by-Step Solution:1) Type EDIT to open the buffer in the external editor, or EDIT file.sql to open a specific file.2) Make changes in the editor and save the file.3) Return to SQLPlus; use LIST to review buffer content or @file.sql to execute.4) Confirm that EDIT is the correct command (not OPEN/SHOW/ALTER). Verification / Alternative check:SQLPlus help (HELP EDIT) documents the behavior and optional filename argument. Why Other Options Are Wrong: OPEN/SHOW/ALTER/WRITE: Not valid SQL*Plus commands for launching the editor; SHOW displays settings, WRITE is not the standard editor launch, ALTER is SQL DDL. Common Pitfalls:Forgetting to set the editor with DEFINE _EDITOR or the environment variable; EDIT then launches the default editor for the platform. Final Answer:EDIT [fileName]

Question 8

SQLPlus Buffer — Viewing the Current Statement Within SQLPlus, which command displays the contents of the SQLPlus buffer (the current SQL statement or statements)?

Accepted Answer

Introduction:SQLPlus keeps a working copy of your most recent SQL or PL/SQL input in an internal buffer. Quickly viewing this buffer avoids retyping and helps with iterative development. This question checks your knowledge of the command used to display the buffer content. Given Data / Assumptions: You are working at the SQLPlus prompt. Some SQL has already been entered or fetched via GET/EDIT. You want to display the buffered text. Concept / Approach:The LIST command (shorthand L) prints the current contents of the SQLPlus buffer to the screen with line numbers. From there you can use APPEND, CHANGE, or EDIT to modify the text, or RUN/; to execute it. LIST is distinct from SHOW, which reports settings (for example, SHOW ALL), and from DESCRIBE (DESC), which inspects object metadata. Step-by-Step Solution:1) At the prompt, type LIST to display the buffered SQL.2) Confirm that the statement shown matches what you intend to execute.3) Make edits if necessary using EDIT or in-line editing commands.4) Execute with RUN or a terminating semicolon. Verification / Alternative check:HELP LIST in SQLPlus documents the command and its options, including listing specific line ranges. Why Other Options Are Wrong: CONTENTS/BUFFER/CURRENT/SHOWSQL: These are not the standard SQLPlus commands for printing the buffer. Common Pitfalls:Confusing LIST with SHOW; SHOW does not print the SQL buffer. Also, remember that the buffer persists only within the current SQL*Plus session. Final Answer:LIST

Question 9

Oracle Backup Types — Consistent vs. Inconsistent In Oracle backup terminology, which backup type ensures that all uncommitted changes have been removed from the datafiles (that is, the database is in a transaction-consistent state at the backup poi…

Accepted Answer

Introduction:Backups can be taken while the database is open or closed. The resulting recovery steps depend on whether the backup captures datafiles in a transaction-consistent state. This question asks you to identify the backup type where uncommitted changes have been cleared from the datafiles. Given Data / Assumptions: A consistent backup is taken when the database is cleanly closed or quiesced. An inconsistent backup is taken while the database is open and requires redo to recover consistency. Terminology distinguishes consistency from scope (full vs. incremental). Concept / Approach:In a consistent backup, the datafiles on disk reflect only committed transactions as of the checkpoint, so recovery does not need to apply redo to make blocks consistent. In an inconsistent backup, redo application (media recovery) is necessary to roll forward uncommitted and committed changes to a consistent point. Therefore, the type that has already removed uncommitted changes is the consistent backup. Step-by-Step Solution:1) Map “uncommitted changes removed” to transaction-consistent datafiles.2) Recognize this corresponds to a consistent backup (typically taken with the database closed).3) Select “Consistent backup.” Verification / Alternative check:Oracle backup and recovery documentation explains that only inconsistent backups require redo application to reach a consistent point-in-time. Why Other Options Are Wrong: Full / Differential / Cumulative incremental: These describe scope, not consistency. Inconsistent backup: Requires redo to become consistent; the opposite of the stated condition. Common Pitfalls:Confusing “full” with “consistent.” You can have a full backup that is either consistent or inconsistent depending on database state at backup time. Final Answer:Consistent backup

Question 10

Oracle DDL — Modifying Table Columns (Identify the NOT True Statement) Which statement about altering table columns in Oracle is NOT true? Choose the single best answer.

Accepted Answer

Introduction:Managing schema evolution safely is a core DBA task. Oracle permits many column alterations online, but each has rules and caveats. This question asks you to identify the statement that is not true about modifying columns, focusing on precision/scale and safe changes. Given Data / Assumptions: ALTER TABLE supports ADD, MODIFY, and DROP COLUMN operations with constraints. Data compatibility determines whether a change is allowed online. Number precision and scale changes are governed by rules related to existing data values. Concept / Approach:Oracle allows increasing column lengths for character types and increasing precision for numeric types because these changes do not risk truncation. For numeric columns, you can also change scale (decimal places). Increasing scale is generally permitted; decreasing scale may be blocked if existing data would be rounded or truncated beyond allowed rules. Therefore, the categorical statement “You cannot increase or decrease the number of decimal places” is not true. The accurate rule is “you can change scale subject to data compatibility.” Step-by-Step Solution:1) Evaluate each claim against Oracle ALTER TABLE rules.2) Recognize that increasing lengths/precision is usually safe; reducing may fail over existing data.3) Note that scale changes (decimal places) are allowed with constraints, so a blanket “cannot” is false.4) Choose the NOT true statement: option (d). Verification / Alternative check:Oracle SQL Language Reference documents MODIFY ... column changes, including permitted adjustments to precision and scale with data checks. Why Other Options Are Wrong: A: Generally allowed; may be restricted by dependencies but is permissible. B: Adding nullable columns is safe and common. C: Increasing size/precision is typically allowed. E: True; reductions can fail due to potential data loss. Common Pitfalls:Forgetting to assess existing data before decreasing sizes or scale; always verify with SELECT checks before ALTER TABLE. Final Answer:You cannot increase or decrease the number of decimal places.

Question 11

SQLPlus Scripts — Default File Extension When saving SQL scripts for use with Oracle SQLPlus, what is the default and conventional filename extension that SQLPlus expects?

Accepted Answer

Introduction:SQLPlus is Oracle’s classic command-line tool for running SQL and PL/SQL scripts. Consistent file naming conventions make automation and execution straightforward. This question checks your knowledge of the conventional default extension used for SQLPlus script files. Given Data / Assumptions: You are saving a SQL script to be executed with START or @ in SQLPlus. Extensions help users and tools recognize file types quickly. The goal is to choose the standard extension used across examples and documentation. Concept / Approach:SQLPlus runs scripts with START file or @file. Although you can execute files without an extension by specifying the exact name, the convention in Oracle documentation and practice is to save SQL command files with the .sql extension. Other extensions (.pls for PL/SQL packages in some shops, .ctl for SQLLoader control files, .ora for network/parameter files) serve different purposes and are not the default for SQLPlus scripts. Step-by-Step Solution:1) Identify the tool and use case: running SQL scripts in SQLPlus.2) Recall standard examples using @script.sql or START script.sql.3) Select .sql as the expected extension. Verification / Alternative check:Oracle tutorials and guides consistently show .sql for SQLPlus scripts, confirming the convention. Why Other Options Are Wrong: .txt: Plain text, not the standard for executable SQL scripts. .pls: Sometimes used informally for PL/SQL sources, but not the SQLPlus default. .ora: Oracle Net and parameter files (tnsnames.ora, sqlnet.ora, init*.ora). .ctl: SQL*Loader control files. Common Pitfalls:Naming scripts inconsistently makes automation harder; standardize on .sql for clarity and tooling support. Final Answer:.sql

Question 12

In Oracle Database, which value-prefixes can be referenced inside row-level triggers to access the old and new row values?

Accepted Answer

Introduction / Context:Oracle Database supports row-level triggers that fire for each affected row during INSERT, UPDATE, or DELETE operations. Inside these triggers, Oracle exposes special pseudo-records to read or assign column values. Knowing which prefixes are available is essential for correct trigger logic. Given Data / Assumptions: The question concerns Oracle row-level trigger syntax. Available prefixes may include :new and :old to reference column values. We assume standard Oracle PL/SQL trigger behavior. Concept / Approach::new refers to the new column values (after change) and :old refers to the original column values (before change). Their availability depends on the DML operation: INSERT provides :new, DELETE provides :old, and UPDATE provides both. Step-by-Step Solution: For INSERT triggers, :new is populated; :old is not meaningful.For DELETE triggers, :old is populated; :new is not meaningful.For UPDATE triggers, both :new and :old are available for comparison and auditing. Verification / Alternative check:Oracle PL/SQL documentation consistently demonstrates :new and :old usage within row-level triggers for all three DML events. Why Other Options Are Wrong::new only and :old only are each incomplete because availability depends on the DML statement. Neither :new nor :old is false since Oracle exposes these pseudo-records in row-level triggers. Common Pitfalls:Trying to assign to :old in UPDATE/INSERT triggers (not allowed). Also, attempting to reference :new in DELETE triggers or :old in INSERT triggers will fail. Final Answer:Both :new and :old

Question 13

In Oracle SQLPlus, after compiling a stored procedure that has errors, which statement correctly describes how to view those compilation errors?

Accepted Answer

Introduction / Context:When compiling PL/SQL objects (procedures, functions, packages) in SQLPlus, syntax or semantic errors are stored in the user_errors/dba_errors views. Developers must explicitly request a listing to diagnose issues. Given Data / Assumptions: The environment is SQLPlus (or tools that mimic its commands). A stored procedure was compiled and has errors. We want the correct way to display those errors. Concept / Approach:SQLPlus provides the command SHOW ERRORS, which displays the latest compilation errors for the most recently created or altered PL/SQL object, including line/position references relative to the database-stored source. Step-by-Step Solution: Compile the object with CREATE OR REPLACE PROCEDURE ...;If errors occur, run: SHOW ERRORS;Optionally, query USER_ERRORS for detailed diagnostics and filtering. Verification / Alternative check:Using SELECT * FROM USER_ERRORS WHERE NAME = 'PROC_NAME' ORDER BY SEQUENCE; confirms the same details that SHOW ERRORS surfaces. Why Other Options Are Wrong:Automatic display (option b) is not the SQLPlus default; you must request errors. Line numbers matching editor (option a) often differ due to stored text formatting. 'NO ERRORS.' (option d) is not the normal success message; SQLPlus typically shows 'Procedure created.' when compilation succeeds. Common Pitfalls:Confusing client editor line numbers with database-stored line numbers; forgetting to use SHOW ERRORS after CREATE OR REPLACE. Final Answer:To see the errors, enter SHOW ERRORS in SQLPlus.

Question 14

Which of the following is NOT a trigger type supported by Oracle Database?

Accepted Answer

Introduction / Context:Triggers in Oracle can fire before or after DML events and, for views, Oracle supports INSTEAD OF triggers. Recognizing valid trigger timing keywords is fundamental for database programming. Given Data / Assumptions: Oracle supports various trigger timings and event scopes. Choices include BEFORE, AFTER, INSTEAD OF, and a distractor. Concept / Approach:Oracle valid timing keywords are BEFORE and AFTER for tables, and INSTEAD OF for views. There is no DURING timing in Oracle trigger syntax. Step-by-Step Solution: List valid keywords: BEFORE, AFTER, INSTEAD OF.Compare with options. DURING does not exist.Select DURING as the incorrect (unsupported) option. Verification / Alternative check:Oracle SQL Language Reference documents only BEFORE, AFTER, and INSTEAD OF timings for triggers. Why Other Options Are Wrong:BEFORE is a standard trigger timing. AFTER is also standard. INSTEAD OF is used on views to substitute DML behavior. Common Pitfalls:Assuming a natural-language timing like DURING exists; Oracle requires precise keywords. Final Answer:DURING

Question 15

In Oracle Database, what does the System Change Number (SCN) represent with respect to database activity?

Accepted Answer

Introduction / Context:The System Change Number (SCN) is a cornerstone of Oracle’s consistency and recovery architecture. Understanding SCN helps explain read consistency, Flashback queries, and media recovery. Given Data / Assumptions: Oracle uses SCNs to label points in time within the database. SCNs advance as changes are committed and recorded. Concept / Approach:SCN is a monotonically increasing logical timestamp. The database increments SCN as changes occur, enabling consistent snapshots and recovery to specific points in time. Step-by-Step Solution: Identify purpose: track the order of changes.Discard options unrelated to change tracking (dirty reads, deadlocks, locks).Select the option describing SCN increments on database changes. Verification / Alternative check:Oracle documentation ties SCN advancement to commit processing and redo generation, enabling features like Flashback Query AS OF SCN. Why Other Options Are Wrong:Dirty reads are prevented by Oracle’s consistency model; SCN is not incremented by them. Deadlocks and explicit locks are concurrency control events, not the basis for SCN increments. Common Pitfalls:Confusing SCN with wall-clock time; SCN is logical, not tied to actual timestamps, though they can be mapped. Final Answer:A value that is incremented whenever database changes are made.

Question 16

Which statement about Oracle and SQL-92 view definitions is NOT true?

Accepted Answer

Introduction / Context:Views encapsulate SELECT queries and present virtual tables to consumers. Standards and vendor implementations impose rules on what clauses can appear directly in view definitions. Given Data / Assumptions: SQL-92 generally disallows ORDER BY directly in a basic view definition unless paired with a top-N limiting construct (vendor-specific). Oracle implements CREATE VIEW and supports querying views like tables. Some ORDER BY usage can be achieved via subqueries or top-N/fetch clauses in newer Oracle releases, but not as a guaranteed ordering attribute of the view. Concept / Approach:The idea that a view can guarantee sorted output is not portable; consumers should specify ORDER BY in their own SELECT. Saying “Oracle views cannot use ORDER BY in view definitions” is not universally correct in modern Oracle syntax (e.g., ORDER BY … FETCH FIRST in the view’s query), and ordering is not guaranteed unless the consumer query specifies it. Step-by-Step Solution: Recognize that CREATE VIEW is supported by Oracle (true).Views are queryable (true).SQL-92 disallows ORDER BY in the simplest view form (true in baseline standard).The blanket claim “Oracle views cannot use ORDER BY” is the least accurate statement when considering practical, modern usage and subquery patterns. Verification / Alternative check:Developers commonly embed ORDER BY in subqueries or use FETCH FIRST with ORDER BY within the view’s defining SELECT in recent Oracle versions; however, consumers should still specify ORDER BY for deterministic ordering. Why Other Options Are Wrong:CREATE VIEW support is standard in Oracle. Views are queryable is intrinsically true. SQL-92 ORDER BY limitation is historically true for bare view definitions. Common Pitfalls:Relying on a view’s embedded ordering for downstream queries; always include ORDER BY in the consumer query. Final Answer:Oracle views cannot use the ORDER BY clause in view definitions.

Question 17

In Oracle SQLPlus, which command displays the structure (columns, types, nullability) of a table?

Accepted Answer

Introduction / Context:DBAs and developers frequently need to inspect a table’s definition (column names, data types, length, and nullability). SQLPlus provides a built-in command to display this metadata quickly. Given Data / Assumptions: The environment is SQLPlus or a client that supports DESCRIBE syntax. We want the canonical, supported syntax to view a table’s structure. Concept / Approach:SQLPlus uses the DESCRIBE command (abbreviated DESC). The correct form is DESCRIBE table_name. The client interprets this meta-command and queries the data dictionary to show columns and attributes. Step-by-Step Solution: Enter: DESCRIBE EMP (for example).Observe output listing column name, type, and nullable status.Use DESC as a shorthand in many Oracle tools. Verification / Alternative check:Equivalent information can be retrieved from USER_TAB_COLUMNS or ALL_TAB_COLUMNS; however, DESCRIBE is the direct SQLPlus method. Why Other Options Are Wrong:STRUCTURE and DESCRIBE STRUCTURE are not SQLPlus commands. DESC TABLE [TableName] adds the word TABLE unnecessarily; the standard form is simply DESC or DESCRIBE followed by the object name. Common Pitfalls:Confusing DESCRIBE with DDL statements; DESCRIBE is a client command, not standard SQL. Final Answer:DESCRIBE [TableName].

Question 18

In SQLPlus, which character terminates a statement so that it is executed when you press Enter?

Accepted Answer

Introduction / Context:SQLPlus collects input lines until a statement terminator is encountered. Understanding terminators is necessary to run SQL and PL/SQL blocks properly. Given Data / Assumptions: We are executing SQL statements interactively in SQLPlus. We need the character that ends the statement. Concept / Approach:Standard SQL statements are terminated with a semicolon in SQLPlus. PL/SQL blocks additionally use a slash (/) on a separate line to execute the compiled block after the ending semicolon of END;. Step-by-Step Solution: Type a SQL statement ending with ; and press Enter.SQLPlus sends the statement to the database for execution.For PL/SQL, after END; press Enter, then type / on a new line to execute the block. Verification / Alternative check:SQLPlus help and examples consistently use ; to terminate SQL statements. Why Other Options Are Wrong:Slash (/) is used to execute the most recent PL/SQL block or the SQL buffer, not to end a single SQL statement line. Colon and period are not SQL*Plus statement terminators. Common Pitfalls:Mixing PL/SQL block execution with single SQL statement execution; forgetting the final ; causes the client to wait for more input. Final Answer:A semicolon ( ; ) followed by Enter.

Question 19

Which SQL referential action is NOT supported by Oracle on foreign keys?

Accepted Answer

Introduction / Context:Referential actions define how child rows respond when parent keys change or are deleted. Different DBMSs implement different subsets of these actions. Given Data / Assumptions: We are considering Oracle Database behavior for foreign keys. Oracle supports ON DELETE CASCADE. Oracle sequences are created/dropped via CREATE SEQUENCE and DROP SEQUENCE (unrelated to FK actions). Concept / Approach:Historically and in mainstream usage, Oracle does not support ON UPDATE CASCADE on foreign keys. If a parent key must change, applications typically update child keys manually or redesign to avoid parent key updates (or use surrogate keys that never change). Step-by-Step Solution: List supported actions: ON DELETE CASCADE is supported.Identify unsupported action: ON UPDATE CASCADE is not supported by Oracle foreign keys.Confirm that sequence DDL is separate from referential actions and is supported. Verification / Alternative check:Oracle DDL will reject ON UPDATE CASCADE in a foreign key definition, requiring manual updates or triggers as a workaround. Why Other Options Are Wrong:ON DELETE CASCADE is supported. CREATE SEQUENCE / DROP SEQUENCE are valid Oracle DDL statements, but are not referential actions. Common Pitfalls:Assuming all ANSI actions are supported on all platforms; Oracle differs from some other RDBMSs like PostgreSQL or MySQL in this respect. Final Answer:ON UPDATE CASCADE

Question 20

In Oracle terminology, which failure describes an abrupt termination of the Oracle instance due to an operating system or hardware failure (for example, power loss)?

Accepted Answer

Introduction / Context:Oracle categorizes failures to guide detection and recovery procedures. Understanding the category helps determine whether instance recovery or media recovery is required. Given Data / Assumptions: Failure is caused by OS or hardware outage (e.g., power failure). Database files are not necessarily corrupted; the instance stopped unexpectedly. Concept / Approach:Instance Failure occurs when the Oracle instance (memory structures and background processes) terminates unexpectedly. On restart, Oracle performs crash (instance) recovery using redo to roll forward and undo to roll back uncommitted work. Step-by-Step Solution: Identify symptom: abrupt stop due to OS/hardware.Map to failure class: Instance Failure.Differentiate from Media Failure (physical I/O corruption) and Application failure (logic errors). Verification / Alternative check:Oracle documentation describes SMON performing instance recovery after abnormal termination. Why Other Options Are Wrong:Application failure refers to errors in the application code. Media Failure involves loss/corruption of database files (needs restore/recovery). Rollback failure is not a standard Oracle failure category. Common Pitfalls:Confusing power failure (instance crash) with datafile corruption (media failure); they require different recovery steps. Final Answer:Instance Failure

Managing Databases with Oracle Questions

Questions

Practice smarter