Oracle Database Interview Questions

This section covers a wide range of Oracle database interview questions, focusing on database structure, backup and recovery, performance tuning, and SQL.

Physical Database Structure in Oracle

The physical database structure in Oracle consists of:

• Data files: Store the actual database data.
• Redo log files: Record changes made to the database (for recovery).
• Control files: Metadata about the database (locations of data files, etc.).

Logical Database Structure in Oracle

The logical structure comprises:

• Tablespaces: Logical containers for database objects.
• Schema objects: Tables, views, indexes, etc.

Tablespaces

A tablespace is a logical grouping of database objects. It's a container for data, indexes, and other database structures.

SYSTEM Tablespace

The SYSTEM tablespace is created automatically when a database is created. It contains the data dictionary (metadata about the database).

Oracle Tables

Tables are the primary structures for storing data in an Oracle database. They are organized into rows (records) and columns (fields).

Oracle Version Numbers

An Oracle version number (e.g., 9.3.0.5.0) has the following components:

• First digit: Major release.
• Second digit: Maintenance release.
• Third digit: Application server release.
• Fourth digit: Component-specific release.
• Fifth digit: Platform-specific release.

BCP (Bulk Copy) in Oracle

BCP (Bulk Copy Program) is a tool for efficiently importing and exporting large amounts of data to and from Oracle tables. It's much faster than row-by-row methods but doesn't copy table structures.

Database, Tablespace, and Data File Relationship

An Oracle database is made up of one or more tablespaces. Each tablespace consists of one or more data files, which are physical files on the operating system.

Snapshots in Oracle

A snapshot is a read-only copy of a table or a set of tables at a specific point in time. This is useful for reporting or creating read replicas.

Hot vs. Cold Backups in Oracle

Oracle Shared Pool Layers

The Oracle shared pool has two main areas:

• Library cache: Stores parsed SQL statements and PL/SQL code.
• Data dictionary cache: Stores metadata about database objects.

Savepoints in Oracle

Savepoints mark points within a transaction, allowing for partial rollbacks. You can roll back to a savepoint without affecting the whole transaction.

Hash Clusters in Oracle

Hash clusters use hash functions to distribute rows across data blocks, improving data retrieval performance for specific queries.

Oracle Database Objects

• Tables
• Tablespaces
• Views
• Indexes
• Synonyms
• Sequences
• Stored Procedures
• Triggers

Pre-select vs. Pre-query Triggers

• Pre-query trigger: Fires before a query is executed.
• Pre-select trigger: Fires after the SQL statement is parsed but before it's executed.

Oracle Forms Modules

• Form modules
• Menu modules
• PL/SQL library modules
• Object library modules

The ANALYZE Command

The ANALYZE command gathers statistics about database objects. The optimizer uses these statistics to create efficient query execution plans.

Creating Synonyms Without a Table

Yes, you can create synonyms that don't point to an existing table. This is useful for creating aliases or for future tables.

Types of Joins in Subqueries

[List and describe types of joins that can be used in subqueries (self join, outer join, equi-join, etc.).]

Oracle Control Files

Control files are crucial for database recovery. They contain metadata about the database's physical structure (data files, redo log files).

Synonyms

Synonyms are aliases for database objects. They can be private (only accessible by the owner) or public (accessible by all users).

Uses of Synonyms

• Hiding the real name and owner of an object.
• Providing public access to a private object.
• Improving location transparency (accessing remote objects).
• Simplifying SQL statements.

Storing Pictures in an Oracle Database

Use the BLOB (Binary Large Object) data type to store images or other binary data in an Oracle database.

BLOB Data Type

The BLOB data type stores variable-length binary data.

TRANSLATE vs. REPLACE

TRANSLATE replaces individual characters. REPLACE replaces character strings.

Savepoints in Oracle

Savepoints allow partial rollbacks within a transaction.

Post-Database Commit vs. Post-Form Commit Triggers

Post-database commit triggers fire after the database has committed a transaction. Post-form commit triggers fire after the form processing, including the database commit, is complete.

Logical Backups in Oracle

Logical backups copy data as a set of SQL statements (using expdp or exp utilities).

Redo Log File Mirroring

Redo log file mirroring creates redundant copies of redo logs for high availability and disaster recovery.

Recursive Hints

[Explain recursive hints in Oracle. These hints might be used to improve query optimization in certain situations where the data dictionary cache is small.]

Limitations of CHECK Constraints

CHECK constraints in SQL can only refer to values within the same row, and they don't typically support subqueries.

IMP (Import) Command Options

[Explain the GRANT, ROWS, INDEXES, IGNORE, and SHOW options of the IMP (Import) command in Oracle. This command is used to import data and metadata from an export dump file.]

Converting Dates to Characters in Oracle

Use the TO_CHAR() function to convert a date value to a character string.

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') FROM dual;

Converting Dates to Characters in Oracle

The TO_CHAR() function converts a date value into a character string. You can specify the desired format.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

Actual vs. Formal Parameters in PL/SQL

In PL/SQL subprograms (procedures and functions):

• Actual parameters: Values passed to a subprogram when it's called.
• Formal parameters: Variables declared in the subprogram's definition.

-- Procedure call (actual parameters)
update_salary(emp_id := 123, salary_increase := 1000);

-- Procedure definition (formal parameters)
CREATE OR REPLACE PROCEDURE update_salary (emp_id IN NUMBER, salary_increase IN NUMBER)
IS
BEGIN
  -- ... procedure logic ...
END;
/

Oracle Reports File Extensions

Oracle Reports uses the .rep (report definition) and .rdf (report definition file) extensions.

Converting Strings to Dates in Oracle

The TO_DATE() function converts a character string to a date value. You need to specify the input string's format.

TO_DATE('string', 'format_mask')

Getting the Current Date and Time in Oracle

The SYSDATE function returns the current database server's date and time. TO_CHAR() can format this value.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

Getting the Current Date in YYYY-MM-DD Format

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;