Oracle interview questions. Most important questions part 1.

1. Tablespaces – Logical storage units.

2. Segments – Space allocated for database objects like tables and indexes.

3. Extents – Contiguous blocks of space within a segment.

4. Data blocks – Smallest units of storage, holding data

Q)State the usage of the INDEXES option in the IMP command.

Solution: The INDEXES option in the IMP command controls whether indexes are imported. Setting it to Y imports indexes, while setting it to N skips them.

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)Give an example to convert a date to a char in Oracle.

Solution: SELECT TO_CHAR(SYSDATE, ‘DD-MM-YYYY’) FROM dual;

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)What is an Oracle table?

Solution: An Oracle table is a structured collection of rows and columns used to store data in a relational format within an Oracle database. Each row represents a record, and each column represents a data field.

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)Explain actual parameters with an example.

Solution: Actual parameters are the values or variables that are passed to a function or procedure when it is called. They provide the input that the function needs to perform its operations.

Example:

Consider a simple Oracle PL/SQL procedure that calculates the square of a number:

CREATE OR REPLACE PROCEDURE calculate_square(p_number IN NUMBER, p_result OUT NUMBER) IS
BEGIN
p_result := p_number * p_number;
END;

In this example:

• p_number is an input parameter.

• p_result is an output parameter.

Calling the Procedure with Actual Parameters:

DECLARE
num NUMBER := 5;
result NUMBER;
BEGIN
— Actual parameters are num and result
calculate_square(num, result);
DBMS_OUTPUT.PUT_LINE(‘The square of ‘ || num || ‘ is ‘ || result);
END;

In this block:

• num and result are the actual parameters passed to the calculate_square procedure.

• The value 5 is passed as num, and the procedure computes the square and stores it in result.

Q) In the Oracle version 9.3.0.5.0, what does each number shows?

Solution: In the Oracle version 9.3.0.5.0, each number represents the following:

1. 9 – Major version number (indicates the major release of the Oracle database).

2. 3 – Minor version number (indicates the minor update or version within the major release).

3. 0 – Patch level (indicates the patch set number).

4. 5 – Build number (indicates the specific build of the patch).

5. 0 – Additional identifier (often used for indicating the release type or specific information about the version).

Q)Give an example to convert a string to a date in Oracle

Solution: SELECT TO_DATE(‘2024-10-16’, ‘YYYY-MM-DD’) FROM dual;

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)What is the relationship among database, tablespace and data file?

Solution:

The relationship among a database, tablespace, and data file is as follows:

1. Database: This is the entire system that stores and manages data, including all the structures and objects (like tables, indexes, etc.).

2. Tablespace: Within a database, a tablespace is a logical storage unit that groups related data. It acts as a container for database objects and helps manage space allocation.

3. Data File: A data file is a physical file on disk that stores the actual data for a tablespace. Each tablespace can consist of one or more data files.

Q)Which function can be used to find the current date and time of the operating system on which the Oracle database is running?

Solution: The function SYSDATE can be used to find the current date and time of the operating system on which the Oracle database is running.

Example-

SELECT SYSDATE FROM dual;

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q) What is bulk copy or BCP in Oracle?

Solution:

Bulk Copy (BCP) in Oracle refers to a method for efficiently transferring large volumes of data between Oracle databases or from external files into an Oracle database. The process is designed to improve performance by minimizing overhead and reducing the number of individual insert statements executed.

Key Features:

• High Performance: BCP can handle large data sets quickly by using bulk operations instead of individual row inserts.

• Data Loading: It is often used for loading data from flat files into tables.

• Minimal Logging: Bulk operations can reduce logging overhead, making it suitable for large-scale data transfers.

Tools:

• SQL*Loader: A utility for loading data from external files into Oracle tables.

• Oracle Data Pump: Used for high-speed data and metadata transfer between Oracle databases.

BCP is particularly useful in data warehousing and ETL (Extract, Transform, Load) processes where large data migrations are common.

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also.

Solution:

Hot Backup

• Definition: A hot backup (or online backup) is performed while the database is actively running and users can access it. The database remains operational during this process.

• How It Works: The backup captures the current state of the database, including active transactions. This is typically done using the ALTER DATABASE BEGIN BACKUP command.

Benefits:

1. No Downtime: Users can continue to access the database during the backup, making it suitable for mission-critical applications.

2. Immediate Availability: The database remains available for transactions, allowing for uninterrupted business operations.

3. Real-Time Backups: Captures data as it is being used, ensuring that the backup is as current as possible.

Cold Backup

• Definition: A cold backup (or offline backup) is performed when the database is shut down and not accessible to users. The database must be in a consistent state for this backup.

• How It Works: The backup includes the entire database files and control files, taken when the database is completely offline.

Benefits:

1. Simplicity: Easier to implement as the database is not active, reducing the complexity of handling active transactions.

2. Consistency: The database is in a consistent state since no transactions are being processed during the backup.

3. Complete Backup: All data files and control files are captured without the risk of including partially written data.

Summary of Differences

Feature	Hot Backup	Cold Backup
Database State	Online (active)	Offline (inactive)
User Access	Available during backup	Not available during backup
Complexity	More complex due to active transactions	Simpler as the database is inactive
Consistency	May require additional steps for consistency	Automatically consistent
Use Cases	Suitable for production environments needing uptime	Useful for maintenance or data migration

Both backup types are essential in different scenarios, and organizations often implement a combination of both to ensure data safety and recovery.

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Q)Explain formal parameters with an example.

Solution:

Formal parameters are the variables declared in a function or procedure that define the expected inputs (arguments) the function will accept. They act as placeholders for the actual parameters passed during a call to the function or procedure.

Example:

Consider a simple Oracle PL/SQL procedure that calculates the area of a rectangle:

CREATE OR REPLACE PROCEDURE calculate_area(length IN NUMBER, width IN NUMBER, area OUT NUMBER) IS
BEGIN
area := length * width;
END;

In this example:

• length and width are formal parameters that accept input values to calculate the area.

• area is an output parameter that returns the calculated area.

Calling the Procedure with Actual Parameters:

DECLARE
rect_length NUMBER := 10;
rect_width NUMBER := 5;
rect_area NUMBER;
BEGIN
— Calling the procedure with actual parameters
calculate_area(rect_length, rect_width, rect_area);
DBMS_OUTPUT.PUT_LINE(‘The area of the rectangle is ‘ || rect_area);
END;

Explanation:

• The formal parameters length, width, and area are defined in the calculate_area procedure.

• When the procedure is called, the actual parameters rect_length, rect_width, and rect_area are passed to the formal parameters.

• The procedure then computes the area based on the provided values and stores the result in rect_area.

Q)What is a snapshot in Oracle database?

Solution:

A snapshot in an Oracle database is a read-only copy of a database object (like a table or a view) that reflects the state of the data at a specific point in time. Snapshots are primarily used for reporting and data warehousing purposes.

Key Features of Snapshots:

1. Read-Only: Snapshots do not allow updates; they are used solely for reading data.

2. Point-in-Time Data: They capture the data from the base table at the time the snapshot was created, providing a consistent view of the data.

3. Remote Access: Snapshots can be created from remote databases, allowing users to access data from different locations without direct connection to the source database.

Q)Write a query to find the average salary of employees from the Emp Oracle Table.

Solution: SELECT AVG(salary) AS average_salary FROM Emp;

Oracle interview questions. Oracle technical interview questions for freshers/Oracle interview questions pyq

Thank You For visiting our website. In our website, you get more such educational content so don’t forget to allow the notification for more such content.

For more visit: https://deepblogs.net/category/educational-courses/