Oracle DBA Interview Questions and Answers for 2025

The key differences between Locally Managed Tablespace (LMT) and Dictionary Managed Tablespace (DMT) are: 

In general, LMT is the preferred choice for most databases as it provides better performance and easier management. However, DMT may be preferred for compatibility with older systems or for specific requirements that LMT does not support.

A password file in Oracle is a file that stores the credentials of database users who are granted the SYSDBA or SYSOPER system privileges. These privileges allow users to perform certain administrative tasks, such as starting and shutting down the database, creating and dropping users, and performing backups and recoveries. To perform these tasks, the user must connect to the database as SYSDBA or SYSOPER and provide the correct credentials stored in the password file. 

The password file is needed because it provides an additional layer of security for the database. Without a password file, anyone with access to the server where the database is installed would be able to connect to the database as SYSDBA or SYSOPER and perform administrative tasks. By using a password file, only those users who have been granted the necessary privileges and have the correct credentials stored in the password file can connect to the database as SYSDBA or SYSOPER.

The differences between SYSDBA and SYSOPER are: 

The use of SYSDBA should be limited to a small group of highly trusted individuals, while SYSOPER can be used by a broader group of administrators for day-to-day tasks. 

The roles of an Oracle Database Administrator (DBA) and an Oracle Developer are distinct and have different responsibilities and skill sets. 

Oracle DBA: 

A DBA is responsible for the installation, configuration, and maintenance of the Oracle database. Duties include creating and managing database objects, monitoring and tuning performance, managing security and backups, and troubleshooting and resolving issues. DBAs also need to be familiar with the underlying operating system, as well as network and storage infrastructure. They also need to be able to work with other teams such as system administrators, network administrators, and security teams. 

Oracle Developer: 

Oracle Developers are responsible for designing and developing the applications that use the Oracle database. They create and modify database objects, write SQL and PL/SQL code, and work with other developers to design and implement the overall application architecture. They also need to be familiar with programming languages, frameworks, and development tools. They work closely with other teams such as business analysts, project managers, and quality assurance teams. 

The similarities in both roles are that both require knowledge of the Oracle database, SQL, and PL/SQL. Both roles also need to understand the data model and be able to write efficient queries. Both roles also need to understand the performance implications of their work and be able to troubleshoot issues. 

An Oracle instance is made up of Oracle Database Memory (Oracle SGA and Oracle PGA) and the Oracle background processes. The SGA (System Global Area) is a shared memory region that contains data and control structures, such as the database buffer cache and the shared pool. The PGA (Program Global Area) is a memory area that contains data and control information for a single Oracle user process. The background processes handle tasks such as managing the database buffer cache, handling I/O, and managing user connections. Together, the SGA and background processes make up the Oracle instance.

When a row is inserted or updated and its size exceeds the available space in the block, the row is split across multiple blocks, resulting in a chain of blocks linked together by pointers. This is called "row chaining". 

Row chaining can be caused by several factors, including 

• Large data types such as LONG or LOB 
• Insufficient PCTFREE space in the table blocks 
• Insufficient INITRANS setting on the table 
• Insufficient storage space  

For Example: Let's say we have a table named "EMPLOYEES" with a row that contains an employee's name and address. The original data can fit in a single data block, but after an update to the employee's address, the data cannot fit in a single block and is split into multiple blocks, resulting in row chaining. 

Row chaining can have a negative impact on the performance of the database so to prevent row chaining, we can follow these best practices: 

1. We can monitor the PCTFREE and PCTUSED parameters which are basically used to control the amount of space reserved in a block for updates, and can be adjusted to prevent row chaining. 
2. The block size can be increased, a larger block size increases the amount of space available for storing data, reducing the likelihood of row chaining. 
3. Data compression can reduce the size of data, reducing the likelihood of row chaining. 
4. By regularly monitoring the size of columns and considering using smaller datatypes reduces the likelihood of row chaining. 

querying the value of the "db_block_size" parameter. 

You can query the V$PARAMETER view by running the following SQL command: 

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size'; 

You can also use the DBA_TABLESPACES view to see the block size for all tablespaces in a database. The BLOCK_SIZE column of the view shows the block size for each tablespace. 

SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;

A staple in Oracle database interview, be prepared to answer this one. Several tools can be used to start up an Oracle database, including 

1. SQL*Plus: A command-line interface that allows you to connect to the database and execute SQL commands. 
2. Enterprise Manager (EM) Database Control: A web-based interface that provides a graphical user interface for managing the database. 
3. Oracle Net Manager: A tool used to configure and manage the network connectivity for the database. 
4. DBCA (Database Configuration Assistant): A graphical tool for creating and managing databases, including starting and stopping the database. 

It is important to note that the method of starting and stopping the database may vary depending on the specific version of the Oracle Database, the operating system, and the configuration of the system. 

There are several types of backups in Oracle, including. 

• Full Backup: A complete backup of all data files, control files, and online redo logs. This type of backup is used to recover the entire database. 
• Incremental Backup: A backup that captures all changes made to the database since the last full or incremental backup. This type of backup is used to reduce the time required to perform a full recovery. 
• Incremental Level 0 Backup: This is a full backup that is performed periodically and all the following incremental backups are taken from this full backup. 
• Incremental Level 1 Backup: This type of backup captures all changes made to the database since the last incremental level 0 or level 1 backup. 
• Differential Backup: A backup that captures all changes made to the database since the last full backup. This type of backup is used to reduce the time required to perform a full recovery. 
• Backup of Control file: A backup of the control file, which contains information about the physical structure of the database and is required for database recovery. 
• RMAN (Oracle Recovery Manager) backup: RMAN is a command-line utility that can be used to perform backups and recoveries of the Oracle database. It is integrated with the Oracle database and can be used to automate backup and recovery tasks.

Hot Backup and Cold Backup are two types of backups in Oracle. 

A Hot Backup is a backup that is taken while the database is still running and in use. In this method, the database is put in "backup mode" during the backup process, which allows the backup to be taken while the database is online. Hot backups are performed using the Oracle Recovery Manager (RMAN) utility. 

A Cold Backup, on the other hand, is a backup taken when the database is shut down. In this method, the database is not running and all the associated files can be copied. Cold backups are typically used in situations where the database cannot be taken offline, such as in a 24x7 production environment. Cold backups can be performed using operating system commands such as cp, tar, or using RMAN.

A control file is a critical component of an Oracle Database that contains information about the physical structure of the database. It is used to manage and track the status of the database and its associated files. The control file contains the following information: 

• The names and locations of the datafiles and online redo log 
• The current status of the database and its associated files 
• The timestamp of the last backup 
• The checkpoint SCN (System Change Number) 
• The archive log sequence number 
• The names and locations of the control files 
• The names and locations of the server parameter file (spfile) 

The control file is read by the Oracle Database instance at startup, and it is updated constantly as the database changes. Oracle Database maintains multiple copies of the control file to ensure high availability and data integrity. In case of control file failure, Oracle can use one of the copies to open the database. 

There are many ways to execute an SQL query, like, 

• SQLPlus: SQLPlus is a command-line tool that is used to execute SQL statements and scripts against an Oracle database. It is included with the Oracle Database software and can be used to run queries, create and modify database objects, and perform other database management tasks. 
• Oracle Enterprise Manager (OEM): OEM is a web-based management tool that can be used to execute SQL queries against an Oracle database. It provides a graphical user interface for managing the database and allows users to run queries, view performance metrics, and perform other tasks without the need for command-line tools. 
• Oracle SQL Developer: Oracle SQL Developer is a free and open-source graphical tool that can be used to execute SQL statements and scripts against an Oracle database. It provides a user-friendly interface for managing the database and allows users to run queries, create and modify database objects, and perform other tasks. 
• Java Database Connectivity (JDBC): JDBC is a Java API that allows Java programs to execute SQL statements and scripts against an Oracle database. It can be used to connect to the database and execute queries, retrieve results, and perform other database management tasks. 

VARCHAR and VARCHAR2 are both variable-length character data types in Oracle Database, but there are a few key differences between them: 

In general, it is recommended to use VARCHAR2 for Oracle databases, as it is specifically designed for the platform and has more advanced features compared to the ANSI standard VARCHAR. 

You can use a regular expression in a SQL query to validate an email address. The regular expression can be used to check for a pattern that matches the format of a valid email address. The specific syntax for the regular expression will depend on the database management system (DBMS) being used, but here is an example of how you can use a regular expression to validate an email address in an Oracle database: 

SELECT email 

FROM your_table 

WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'); 

This query will select all email addresses from the table "your_table" where the email address matches the pattern specified in the regular expression. The regular expression check for the pattern of the email address, it starts with one or more characters that can be alphanumeric or one of the following characters: '.', '_', '%', '+', '-', it then checks for the presence of '@' character and then again one or more characters that can be alphanumeric or one of the following characters: '.', '-', then it checks for the presence of '.' and then two or more alphabets.

The SGA (System Global Area) is a memory area used by the Oracle Database to store data shared among all server and background processes. To check the amount of free memory available in the SGA, you can query the V$SGA view in the SYS schema. 

You can check the free memory in SGA by querying the V$SGA view and looking at the FREE_MEMORY column. 

You can also query the V$SGASTAT view to see the statistics of the different SGA components, including the amount of free memory for each component. 

This will give you the breakdown of Free Memory available in the Buffer Cache and other SGA components. 

It is important to keep an eye on the amount of free memory in the SGA, as a lack of free memory can lead to performance issues and even the inability to allocate memory for new processes. 

There are several methods to shutdown an Oracle database, including

1. Using the SHUTDOWN command in SQL*Plus: This command can be used to shut down the database immediately or after a specified delay.
2. Using the srvctl command: This command can be used to shut down an Oracle RAC database or a single-instance database.
3. Using the start and dbshut scripts: These scripts are located in the Oracle home directory and can be used to start and stop an Oracle database.
4. Using the Oracle Enterprise Manager (OEM): This web-based tool can be used to manage and monitor an Oracle database, and it can be used to shut down a database.
5. OS commands such as shutdown -h now for Linux or shutdown /s /t 0 for Windows.

In Oracle, there are two types of rollback segments: System rollback segments and Temporary rollback segments. 

System rollback segments are created during the database creation and are used for recovery purposes. They are used to undo changes made to the database by transactions that are later rolled back, and they are also used by Oracle to recover the database in the event of an instance failure. 

Temporary rollback segments are created as needed by Oracle, and they are used for short-term undo operations such as sorting and creating indexes. They are automatically dropped when they are no longer needed. 

A stored procedure and a database trigger are both used to encapsulate a set of database operations, but they have different purposes and advantages. Some of the differences between are -

Stored procedures and database triggers can both be used to implement business logic in a database, but the former is more flexible and customizable, while database triggers are more focused on automatically enforcing rules and constraints. 

A query used in the top-N analysis is a query that returns a specific number of top-ranked or highest-valued records from a table or query result. The syntax of such a query will depend on the specific database management system (DBMS) being used, but a common structure is to use the SELECT statement with a LIMIT or TOP clause, along with an ORDER BY clause to specify the ranking criteria. Here is an example of a top-N query in SQL: 

SELECT * 
FROM table_name 
ORDER BY column_name DESC 
LIMIT N; 

This query will select all columns from the table named "table_name" and will order the results by "column_name" in descending order and will return only N number of rows. 

DML (Data Manipulation Language) operations, such as INSERT, UPDATE, and DELETE, can be performed on views, but some restrictions must be considered. 

• The view must be based on a single base table: DML operations are not allowed on views that are based on multiple tables, also known as "complex views" or "join views." 
• The view must not have any aggregate functions: DML operations are not allowed on views that use aggregate functions such as SUM, COUNT, AVG, etc. 
• The view must not have any group by clause: DML operations are not allowed on views that use GROUP BY clause. 
• The view should not contain a DISTINCT keyword: DML operations are not allowed on views that use the DISTINCT keyword. 
• The view must not have any subqueries: DML operations are not allowed on views that use subqueries. 
• The view must not have any read-only columns: DML operations are not allowed on views that have any read-only columns. 
• The view must not have any expressions or calculations: DML operations are not allowed on views that use expressions or calculations. 
• The view should not have any Check Constraints: DML operations are not allowed on views that have any check constraints. 

Triggers in Oracle are special stored procedures that are automatically executed in response to specific events or changes to the database. 

The types of triggering events in Oracle include: 

• DDL (Data Definition Language) Triggers: fired in response to specific database structure changes such as CREATE, ALTER, DROP, TRUNCATE, etc. 
• DML (Data Modification Language) Triggers: fired in response to data changes such as INSERT, UPDATE, DELETE. 
• System Triggers: fired in response to system-level events such as user logon/logoff, database startup/shutdown, and others. 
• INSTEAD OF Triggers: used to modify the data in views, which are otherwise read-only. 
• Compound Triggers: used to handle multiple triggering events in a single block of code. 

Triggers can be used to enforce complex business rules, validate data integrity, and enforce security. 

The differences between views and synonyms in Oracle database is: 

Views are used to simplify complex queries and data access, while synonyms are used to simplify object naming and access for users. Views can provide data security by restricting data access through the view definition, while synonyms do not have any data restrictions. 

The LONG data type in Oracle is a deprecated data type used for storing character or binary data up to 2 gigabytes in length. 

In Oracle, the LONG data type was primarily used for storing large amounts of text or binary data. This type of dataset is often encountered in big data and data science applications, where the amount of data being analyzed is too large to fit in the memory of a single computer or cluster. However, it has several limitations, such as the inability to be used in most SQL functions, the requirement for a special SELECT statement syntax to retrieve data, and the inability to participate in transactions or rollbacks. 

Due to these limitations, the LONG data type has been deprecated and replaced by the LOB (Large Object) data types, such as CLOB (Character Large Object) and BLOB (Binary Large Object), which offer more features and capabilities. 

To use the LONG data type in Oracle, you would define a column in a table with the LONG data type, and then store the data in that column. To retrieve data stored in a LONG column, you would use a SELECT statement with the "FROM_LOB" clause, for example: 

SELECT * FROM my_table WHERE column_name = FROM_LOB(:value); 

In a database, locking is the mechanism used to prevent multiple transactions from accessing the same data at the same time, which can lead to conflicts and data inconsistencies. Locking can be either implicit or explicit. 

Implicit locking is the default method for controlling concurrent access to data in a tabular database and provides basic protection for data consistency, while explicit locking provides more control and can be used for more advanced use cases. 

The error ORA-03113 means that the end-of-file on the communication channel has been reached. This is a general error message indicating that the connection between the client and the server has been lost. It can be caused by a variety of factors such as 

• The server process being terminated unexpectedly. 
• The network connection between the client and the server has been lost or disconnected. 
• The client or server machine has been rebooted or shut down. 
• The listener process is not running or has failed. 

This error can happen in different scenarios, for example, when a client application is trying to execute a query and the connection to the server is lost, or when the client is trying to log in to the database and the connection is lost. 

The solution to this error is to troubleshoot the root cause of the problem, it could be network-related, server-related, or client related. 

• Check the network connection, and make sure the client can reach the server. 
• Restart the listener process. 
• Restart the server process. 
• Check the alert log file for any error messages. 
• Check the client connection settings, and check if the SID or service name is correct. 

There are several ways to find the name of a constraint in an Oracle database: 

• Using the ALL_CONSTRAINTS view: 

SELECT constraint_name, table_name, constraint_type 
FROM all_constraints 
WHERE table_name = 'your_table_name'; 

This query will return the constraint name, table name, and constraint type (e.g. PRIMARY KEY, FOREIGN KEY) for all constraints on a table called "your_table_name". 

• Using the USER_CONSTRAINTS view: 

SELECT constraint_name, table_name, constraint_type 
FROM user_constraints 
WHERE table_name = 'your_table_name'; 

This query will return the constraint name, table name, and constraint type for all constraints owned by the current user on a table called "your_table_name". 

• Using the DBA_CONSTRAINTS view: 

SELECT constraint_name, table_name, constraint_type 
FROM dba_constraints 
WHERE table_name = 'your_table_name'; 

This query will return the constraint name, table name, and constraint type for all constraints in the database on a table called "your_table_name". 

• Using the data dictionary views: 

SELECT constraint_name 
FROM dba_cons_columns 
WHERE table_name = 'your_table_name' 
AND column_name = 'your_column_name'; 

This query will return the constraint name for the specific table and column. 

These queries will work if the constraint is enabled, if the constraint is disabled it will not show up in the result. 

Polymorphism in an Oracle database refers to the ability of objects or methods to take on different forms based on the context in which they are used. 

In an Oracle database, polymorphism is implemented using object types, which allows us to define custom data structures that can store and manipulate data in a structured and encapsulated manner. We can create methods associated with an object type, and these methods can be applied to any instance of the object type, regardless of the specific data stored within the object. 

For example, we can create an object type to represent a person and include attributes such as name, age, and address. We can then create different object types that inherit from the person object type, each representing a specific type of person, such as an employee or a customer. The same methods can be applied to all instances of the person object type, regardless of the specific type of person they represent. 

Polymorphism in an Oracle database allows us to write flexible and reusable code, making it easier to manage and maintain large and complex databases. It also supports the implementation of object-oriented programming principles, allowing you to model real-world objects and their interactions in a database. 

A hierarchical profiler is a tool that can be used to analyze the performance of a program or application by measuring the time spent in different sections of the code, also known as "call stacks" or "code paths". The profiler can be used to identify and analyze performance bottlenecks and memory usage issues, by providing detailed information about the execution time and memory usage of different sections of the code. 

The hierarchical profiler typically works by instrumenting the code, which means adding extra code to measure the performance of specific sections of the code. This instrumentation can be done at the source code level, or it can be done dynamically by attaching the profiler to a running process. 

The profiler then captures performance data, such as execution time and memory usage, for each section of the code. The data is then analyzed and presented in a hierarchical format, which allows the user to see the performance of the code at different levels of granularity. This hierarchical format can help the user to identify performance bottlenecks by drilling down into specific sections of the code, and it can also help the user to understand the flow of execution through the code. 

Hierarchical profilers are commonly used in software development and optimization, performance testing, and debugging. They can be used to optimize the performance of an application by identifying and fixing performance bottlenecks, and they can also be used to identify and fix memory leaks or other memory-related issues. 

Dynamic SQL in an Oracle database refers to the ability to construct and execute SQL statements dynamically, at runtime, rather than at compile-time. This allows you to generate and execute SQL statements based on conditions, parameters, or other factors that may not be known until runtime. 

Dynamic SQL is often used in situations where you need to generate and execute SQL statements based on user input, changing conditions, or other factors. For example, you might use dynamic SQL to build and execute SQL statements based on the values entered by a user into a form, or to build and execute SQL statements based on the results of another query. 

To use dynamic SQL in an Oracle database, you would typically use the EXECUTE IMMEDIATE statement, which allows you to dynamically construct and execute a SQL statement as a string. For example: 

DECLARE 
sql_stmt VARCHAR2(1000); 
BEGIN 
sql_stmt := 'SELECT * FROM my_table WHERE column_name = ' || :value; 
EXECUTE IMMEDIATE sql_stmt; 
END; 

Dynamic SQL can be useful in situations where you need to generate and execute SQL statements dynamically, but it should be used with caution, as it can increase the risk of security vulnerabilities such as SQL injection attacks. It is also important to carefully validate user input and sanitize any user-supplied data before using it in a dynamic SQL statement.

Below are some of the important views in Oracle database: 

• DBA_TABLES: It contains information about all tables in the database. 
• DBA_OBJECTS: It contains information about all objects in the database, including tables, views, indexes, and other types of objects. 
• USER_TABLES: It contains information about all tables owned by the current user. 
• ALL_TABLES: It contains information about all tables accessible to the current user. 
• V$SQL: It contains information about SQL statements currently in the shared SQL area. 
• V$SESSION: It contains information about current database sessions. 
• V$SQLSTATS: It contains performance statistics for SQL statements. 
• V$SYSTEM_EVENT: It contains information about system events and waits. 
• V$DATAFILE: It contains information about datafiles in the database. 
• DBA_INDEXES: It contains information about all indexes in the database. 

These views provide useful information for monitoring the performance, health, and metadata of an Oracle database, and are often used by database administrators, developers, and performance analysts. 

No, it is not possible to take a snapshot of the storage while the Oracle database is open and ensure that no data is written to the data files. To achieve a consistent snapshot, the database must be in a consistent state, which requires shutting down the database. After the database is shut down, the storage system can take a snapshot of the data files. This will capture a point-in-time image of the data that can be used for backup purposes. Once the snapshot is taken, the database can be restarted.