Oracle Deep Dive

Question 1

What are Oracle-specific data types?

Answer 1

Oracle includes data types like NUMBER, VARCHAR2, DATE, CLOB, BLOB, and RAW, tailored for its storage and performance needs.

Question 2

What is the ROWNUM pseudocolumn in Oracle?

Answer 2

ROWNUM assigns a unique number to each row returned by a query, starting from 1, used for limiting rows.

Question 3

What is the ROWID pseudocolumn in Oracle?

Answer 3

ROWID represents the unique physical address of a row in a table and can be used for fast access.

Question 4

What are sequences in Oracle?

Answer 4

Sequences are database objects that generate unique numeric values, often used to simulate auto-incrementing fields.

Question 5

What are triggers in Oracle used for?

Answer 5

Triggers are procedures that automatically execute in response to specific events on a table or view.

Question 6

What is the MERGE statement in Oracle?

Answer 6

MERGE allows conditional INSERT or UPDATE operations in one statement, also known as ‘upsert’.

Question 7

What is PL/SQL in Oracle?

Answer 7

PL/SQL is Oracle’s procedural language extension to SQL, allowing control structures, loops, and exception handling.

Question 8

What are PL/SQL packages?

Answer 8

Packages group procedures, functions, variables, and types into a single unit for modular programming and reuse.

Question 9

What are PL/SQL cursors?

Answer 9

Cursors let you fetch and process query result rows one at a time within PL/SQL blocks.

Question 10

What is exception handling in PL/SQL?

Answer 10

It allows handling runtime errors using EXCEPTION blocks to improve program robustness.

Question 11

What are optimizer hints in Oracle?

Answer 11

Optimizer hints override the default execution plan chosen by the Oracle optimizer.

Question 12

What is SQL*Plus?

Answer 12

SQL*Plus is a command-line interface for running SQL and PL/SQL commands in Oracle.

Question 13

What is Oracle SQL Developer?

Answer 13

A graphical tool for querying, managing, and debugging Oracle databases.

Question 14

What is an advantage of Oracle pseudocolumns?

Answer 14

They provide internal metadata access useful for pagination and debugging.

Question 15

What is a disadvantage of relying on ROWNUM?

Answer 15

ROWNUM can behave unpredictably with ORDER BY unless used in subqueries.

Question 16

What’s a best practice when using sequences?

Answer 16

Use them with triggers or default clauses to ensure unique keys and avoid concurrency issues.

Question 17

What’s a common use case for the MERGE statement?

Answer 17

Synchronizing data between staging and target tables without multiple statements.

Question 18

What’s a use case for PL/SQL packages?

Answer 18

Encapsulating related procedures like billing or user management into logical units.

Question 19

What is the architectural impact of using packages?

Answer 19

Promotes modularization, version control, and encapsulation in large Oracle applications.

Question 20

What is a performance benefit of optimizer hints?

Answer 20

Helps fine-tune execution plans for critical queries when the optimizer underperforms.

Question 21

What’s a fault tolerance issue with triggers?

Answer 21

Poorly written triggers can cause unintended side effects or recursive execution.

Question 22

What’s a common monitoring approach for Oracle performance?

Answer 22

Using tools like AWR, ADDM, and SQL Trace to analyze query and system performance.

Question 23

What is a real-world tradeoff when using MERGE?

Answer 23

While convenient, MERGE can be slower than separate INSERT/UPDATE for simple cases and harder to debug.

Question 24

What’s a gotcha with ROWID usage?

Answer 24

ROWID changes if the row is moved (e.g., via export/import), so it shouldn’t be used as a permanent identifier.

Question 25

What’s a potential issue with optimizer hints?

Answer 25

Hints can become outdated and mislead the optimizer as data or schema changes.

Question 26

What’s a best practice for exception handling in PL/SQL?

Answer 26

Catch only expected exceptions and re-raise unknown ones to avoid hiding bugs.

Question 27

What’s a common interview question on Oracle sequences?

Answer 27

How do you use sequences to implement auto-increment behavior in Oracle?

Question 28

What’s an advantage of SQL Developer over SQL*Plus?

Answer 28

SQL Developer provides a user-friendly GUI, integrated debugging, and better development experience.

Question 29

What’s a use case for cursors in PL/SQL?

Answer 29

Processing row-by-row logic like generating customized reports or aggregating data.