Oracle Interview Questions and Answers for 2024 Web Development

Expect to come across this popular question in Oracle interview questions and answers

SQL is an abbreviation for Structured Query Language. SQL is a programming language that is used to connect with a server in order to access, alter and control data. 

SQL statements are classified into five categories. 

• SELECT Data Retrieval 
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE AND MERGE 
• CREATE, ALTER, DROP, RENAME, TRUNCATE are Data Definition Language (DDL) commands. 
• COMMIT, ROLLBACK and SAVEPOINT are Transaction Control Statements. 
• DCL (Data Manipulation Language): GRANT, REVOKE 

An alias is a user-defined alternate name for a column or table. Alias heads appear in upper case by default in columns. To make the alias case-sensitive, enclose it in double quotation marks (" ") The "AS" keyword before the alias name simplifies the SELECT clause. 

Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias). 

A Literal is a string that can contain a character, a number, or a date and is not a column name or alias and is included in the SELECT list. Date and character literals must be surrounded by single quotation marks (' '), but numeric literals do not have to be. 

As an example: Choose last_name||'is a'||job_id As "emp information" from the employee; ("is a" is a literal here). 

SQL Functions are an extremely powerful SQL feature. SQL functions can accept parameters but must always return a value. 1 

SQL functions are classified into two types: 

1. Single-Row Functions: These functions work on a single row to return a single result. 

Single-Row Function Types: 

• Character 
• Number  
• Date 
• Conversion  
• General 

2. Multiple-Row Functions: These functions act on groups of rows to return a single result for each group of rows. 

Multiple-Row Function Types: 

• AVG  
• COUNT 
• MAX  
• MIN 
• SUM  
• STDDEV  

In Java, the Java virtual machine controls the thread's life cycle. The following are the java thread states: 

• New  
• Runnable 
• Running 
• Non-Runnable (Blocked) 
• Terminated  

NEW denotes a newly established thread that has not yet begun execution. RUNNABLE is either running or ready to be executed, but it is awaiting resource allocation. BLOCKED is awaiting a monitor lock in order to enter or re-enter a synchronized block/method. If the thread scheduler has picked it, it is in the operating state. When the run() method of a thread terminates, it is said to be terminated or dead. 

This is a frequently asked question in Oracle interview questions.

The javac command reads source files containing Java module, package, and type declarations and compiles them into class files that operate on the Java Virtual Machine. Annotations in Java source files and classes can also be processed by the javac command. 

In Java, a Wrapper class is a type of class that offers a method for converting primitive data types into objects and vice versa. When a wrapper class is constructed, a new field is generated in which the primitive data types are stored. 

• Using SQL*Plus as a command-line tool. You may directly perform SQL statements using this tool. 
• Using a graphical user interface (GUI) tool such as SQL Developer. Such tools allow you to run SQL commands directly. 
• Oracle Enterprise Manager is being used. This is an indirect method of running a SQL query. When you use Oracle Enterprise Manager, some activities are implicitly transformed to SQL queries, which are then run against the database. 
• Create your own software. This is not a traditional method of running your queries, although it is often used. SQL queries are executed by any online or Windows software that uses an Oracle database as its backend. These applications are developed in a programming language such as.NET or JAVA, and they link to the database via a driver. 

One of the most frequently posed Oracle basic interview questions, be ready for it.

To acquire information about the existing users in the password file, use the "v$pwfile users" view. Run the SQL query below: 

SELECT * FROM v$pwfile users in sql; 

For each user in the password file, the query above will return four columns. USERNAME, SYSDBA, SYSOPER, and SYSASM are the column names. 

• The USERNAME field displays the user's username from the password file. 
• The SYSDBA field indicates whether or not the user has SYSDBA rights. 
• The SYSOPER field indicates whether or not the user has SYSOPER rights. 
• The SYSASM field indicates whether or not the user has SYSASM rights. 

In the Oracle database, the basic unit of data storage is the table. Data is organized into rows and columns. 

To construct a new table, use the "CREATE TABLE" command. 

In Oracle, a nested table is a data type that has columns for storing multi-valued properties. An whole sub table can be stored in a nested table. 

There are more Oracle interview questions and answers related to this topic on net.

One or more tablespaces or logical storage units can be found in an Oracle database. These tablespaces hold all of the data in a database. Each tablespace is made up of one or more data files. The data files are physical entities that adhere to the operating system on which Oracle is installed.

The command "ANALYZE" lets the user to execute numerous operations on an index, table or cluster. It is beneficial to: 

• To detect the table's or cluster's migrated and chained rows. 
• To validate an object's structure. 
• To collect statistics about objects used by the user and put them in the data dictionary. 
• To remove statistics from the data dictionary that were utilized by an item. 

The merge statement enables for the subsequent combining of data from two tables. It chooses data from the source table and inserts/updates it in the target table based on the query condition. Merge is a helpful command in data warehouse systems.

You can learn more about DBA from our Oracle DBA interview questions and answers site.

Oracle is a corporation. Oracle is a database server that maintains data in a highly organized manner. It enables users to save and retrieve relevant data in a multi-user environment, allowing several users to access the same data at the same time. All of this is done while maintaining great performance. A database server also prevents unwanted access and provides effective failure recovery options. A standby database is a database replica that is formed by backing up a primary database.

A DBA can create new users, delete current users, and change any environment variables or rights granted to other users. 

• Control database storage 
• Manage users and security 
• Organize schema objects 
• Monitoring and management of database performance Backup and recovery 
• Job scheduling and automation 

It's no surprise that this one pops up often in Oracle interview questions software engineer.

Because the backup is taken while the database is alive and operating, it is also known as an online backup. And when the backup can only take place when the database is shut down, it is referred to as Cold Backup, which is also known as Offline Backup.

Passwords for database users are saved in the database's data dictionary. When a user tries to log into the database, his or her username and password are compared to the values in the database. If the username and password match, the user is granted database access. The data dictionary is kept in the database and may be accessed as long as the database is open. The dictionary also contains administrator passwords. 

The data dictionary would be inaccessible until the database was locked. Because one of the administrator's responsibilities is to restart a down database, they must be able to log in even if the database is locked. That's where the password file comes in. A password file is an operating system file that is kept on a separate disc from the database. It saves the login and password for users with SYSDBA or SYSOPER access. When the database is unavailable, administrators with particular privileges are authenticated using password files. 

Data files contain all of the tables. The data file contains all of the data. The database data is stored in the data files. The data of relational database structures, such as tables and indexes, is physically stored in database data files.

Normalization is the process of organizing data in a database. This entails creating tables and developing relationships between them in accordance with rules aimed to secure data while also making the database more adaptable by eliminating redundancy and conflicting dependencies.

The recovery catalog is a database architecture that holds the metadata for data restoration and recovery activities that RMAN administers. A recovery catalog stores the metadata utilized by RMAN throughout the reinstallation and healing processes. Even if the target control file and its backups are deleted, the RMAN information remains in the recovery catalogue.

The benefits of utilizing a view in a table are as follows: 

• It is a subset of the information in the table. 
• It saves complicated inquiries. 
• It may combine numerous tables into one. 
• It takes up very little space. 
• It provides data from various angles. 

A common question in Oracle interview questions advanced, don't miss this one.

Oracle provides numerous methods for shutting down the database. 

• Normal Mode: By default, the database is shut down in normal mode. It is appropriate when no other clause is present. Before shutting down the database, it waits for all presently associated users to disconnect, and no new connections are authorized until the statement is published. The command line is as follows: 

SHUTDOWN NORMAL 

• Transactional Mode: Transactional mode aids in the shutdown of the database while enabling ongoing transactions to be completed. No new connections are allowed during this procedure, and the database is shut down when all transactions have completed. To shut down the database, use this command line. 

SHUTDOWN TRANSACTIONAL 

• Immediate Mode: When you know a power loss is imminent, you can activate urgent mode. All sessions will be disconnected, all active transactions will be rolled back, the database does not wait for existing database users to quit before proceeding, and the database will be shut down. There will be no requirement for instance recovery on the next startup. 

To instantly shut down a database, use the command SHUTDOWN with the IMMEDIATE argument.  

SHUTDOWN IMMEDIATE 

• Abort Mode: If you want to shut down the database immediately without any active transactions, use this abort method. No new connections or transactions may be established after the statement has been issued. The Oracle database ends all current client SQL queries automatically and does not wait for existing database users to disconnect. Uncommitted transactions will not be rolled back. The database automatically disconnects all related users. 

Use the SHUTDOWN command with the ABORT clause: 

SHUTDOWN ABORT 

A query is added to any view to define particular table rows and columns. A virtual table is a form of view. There are two sorts of views available: read-only and read-write.

• PL/SQL is a SQL Language procedural language extension. 
• Oracle debuted PL/SQL. 
• It is a blend of SQL and Procedural Statements that is used for application development. 
• When we send PL/SQL, we are essentially submitting a block-structured programming language. 
• Blocks are then used to run all SQL statements and procedure statements independently using the SQL engine. 
• Explain your current and prior projects, as well as your duties and responsibilities and explain some of the significant challenges you've encountered while working with PL/SQL. 

You will get different answers on this topic from Oracle PL/SQL interview questions and answers on-site. 

A staple in Oracle interview questions for experienced, be prepared to answer this one.

Triggers are similar to stored procedures, they are automatically triggered anytime when a DML operation is done on a table or view. 

PL/SQL supports two different types of triggers. 

• Trigger at the Statement Level 
• Trigger for Row Level 

Statement Level Trigger: A statement-level trigger executes the trigger body just once for the DML statement.

Row Level Trigger: A row-level trigger executes the trigger body for each row of DML statement. This is why we utilize each row clause and internally stored DML transaction in the trigger specification; these qualifiers: old, new, are also known as records type variables. 

These qualifiers are utilized in the trigger specification as well as the trigger body. 

• Syntax old.column_name 
• Syntax new.column_name 

When we use these qualifiers in trigger specifications, we cannot include ":" in the names of the qualifiers. 

Declare  

a exception  

begin  

If to_char(sysdate, ‘DY)=’THU’ 

 then raise a;  

end if;  

exception  

when a then  

dbms_output. put_line(‘my exception raised on thursday’);  

end 

; 

Output: On Thursday, I filed an exception. 

These are the advantages of PL/SQL packages. 

• Functions and processes can be stored in a single unit known as a package. 
• Packages offer security in order to grant rights. 
• Within the package, functions and procedures share a common variable. 
• Packages provide assistance even if the functions are overloaded. 
• Even when several items are put into memory, packages improve speed. 

PL/SQL is a SQL extension that is used in Oracle. PL/SQL combines SQL's data manipulation strength with procedural language processing capabilities to construct super-powerful SQL queries. It utilizes SQL to tell the compiler what to do and a procedural method to tell it how to accomplish it.

TRANSLATE is used to replace a character with another character, whereas REPLACE is used to replace a character with a word.

• Oracle Software Installation 
• Oracle database development 
• Upgrading the database and applications to the latest release levels 
• Database startup and shutdown 
• Managing the storage structures of a database 
• Security and user management 
• Tables, indexes, and views are examples of schema objects that may be managed. 
• Making backups of databases and recovering them when needed 
• Monitoring the database's health on a regular basis and implementing preventative or remedial steps if needed 
• Performance evaluation and optimization 

In a small-to-medium-sized database system, a single DBA may be the sole individual handling all of these activities. In big business setups, the role is frequently shared among numerous DBAs designated Database Security Administrator or Database Tuning Expert, each with his or her specific area of specialization. 

You may create a database using one of three tools: 

• SQL *Plus: The SQL *Plus starting command may be used to start an Oracle database instance. 
• Oracle Enterprise Manager: This is a system management tool that may also be used to start the Oracle database. It offers an all-in-one solution for controlling your heterogeneous environment. Even if the database is halted you can still log in to OEM. It will display the Startup button after detecting the condition of the down database. 
• Recovery Manager: RMAN is also known as the RMAN repository, which is linked with the TARGET keyword and is also a database on which RMAN executes backup and recovery operations in the database's control file. 

This is the fundamental syntax for determining the indexes for a table in Oracle. 

SELECT owner, index_name, 
tablespace_name, status 
Set pages 
Break on table_name on index_name 
column table_name 
column index_name 
column column_name 
select table_name, index_name, column_name 
from dba_ind_columns 
where table_owner 
order by table_name, index_name 
column_position 

There is the possibility of several database context processes. They are known as "DBWn" in the operating system. This technique is responsible for storing "dirty" buffers on disk. When a server process has to update a data block, it reads it from disk into the buffer cache if it isn't already there, then updates the cache copy. As a result, a "dirty" block is an updated database block in the buffer cache.

VARCHAR and VARCHAR2 are Oracle data types that are used to hold variable-length character strings. Their distinctions are as follows: 

• VARCHAR can hold up to 2000 bytes of data, whereas VARCHAR2 can hold up to 4000 bytes. 
• VARCHAR will keep the space for characters defined upon declaration even if not all of them are used, whereas VARCHAR2 will free the unused space. 

RAW datatype are used to store variable-length binary data such as byte strings. 

The distinction between RAW and VARCHAR2 datatype is that PL/SQL does not recognize this data type and so cannot perform any transformations when transferring RAW data to various systems. This sort of data can only be queried or added into a table. 

Syntax: RAW (precision) 

A Primary Key is used to uniquely identify each table row, whereas a Unique Key prohibits duplicate entries in a table column. 

Here are a few distinctions: 

• On the table, the primary key can only be one, whereas unique keys can be multiple. 
• The main key cannot include any null values, however the unique key can have numerous null values. 
• A clustered index is used for the main key, but a non-clustered index is used for the unique key. 

In Oracle, we may determine the current date and time by using the SYSDATE command. 

Syntax: 

SELECT SYSDATE into CURRENT_DATE from dual; 

Expect to come across this popular question in Oracle interview questions.

The GROUP BY clause is used to identify and group data in the query results by one or more columns. This clause is frequently used with aggregate functions like as COUNT, MAX, MIN, SUM, AVG and so on. 

Syntax: 

SELECT COLUMN_1, COLUMN_2 
FROM TABLENAME 
WHERE [condition] 
GROUP BY COLUMN_1, COLUMN_2 

If the columns and relative data types in the source tables are the same, the set operator allows the user to retrieve data from two or more tables at once. 

• Except for duplicate entries, the UNION operator returns all rows from both tables. 
• UNION ALL- it returns all the rows from both the tables along with the duplicate rows. 
• MINUS- it retrieves rows from the first table that are not present in the second. 
• INTERSECT – it returns just the rows that are shared by both tables. 

On a higher level, there are two types of backup accessible in Oracle: physical and logical. 

• Physical backup creates and saves copies of physical database files (such as data files, control files, redo logs and other executables) for the future. This can be accomplished through the use of either operating system utilities or RMAN. 
• In contrast, logical backup allows you to backup specific database objects like as tables, views, indexes, stored procedures and so on using Oracle's Export/Import application. 

Backup files are copied from the hard drive, media, or tapes to the restoration site during the restoration process, and the database is then made operational.  

Recovery includes an extra step of updating these data files using redo logs in order to restore changes that were not backed up. 

Let us look at this through the lens of a situation. 

On Friday at 11 p.m., a thorough backup of the database is performed. 

At 7 a.m. on Saturday, the database crashed. 

We can restore the missing files using the Restoration 11 PM complete backup. However, the data will not be recovered until Friday at 11 p.m., rather than Saturday at 7 a.m. To achieve the same result, redo logs may be used, bringing the database to the point of failure. 

Oracle has introduced flashback technology to restore database object states from the past. It can also retrieve inadvertent modifications that were committed. The value of the UNDO RETENTION option determines recovery. 

For example, if the UNDO_RETENTION parameter is set to 2 hours and a user deletes the data by accident at 11 a.m. with a commit done. Using FLASHBACK QUERY, he may then get these rows just until 1 PM. 

Clustering is a component of grid architecture that focuses on a certain goal. 

While a grid may or may not consist of numerous clusters, it does include a larger structure that allows for the sharing of storage systems, data resources and other resources across different geographical areas. 

A cluster will have a single owner, however the grid might have several owners depending on how many clusters it houses. 

There are two kinds of optimizers: 

• Rule-Based Optimizer (RBO): RBO is utilized if the referenced objects do not keep any internal statistics. 
• Cost-Based Optimizer (CBO): If the referenced objects keep internal statistics, the CBO will examine all potential execution plans and choose the one with the lowest cost. 

• DISPATCHERS: Sets up dispatcher processes in a shared server architecture. 
• MAX _DISPATCHERS: The maximum number of dispatcher processes that can run concurrently is specified by this parameter. 
• SHARED_SERVERS: The server will only have a few shared server processes. The number of shared servers is determined during the starting process. 
• MAX_SHARED_SERVERS: This parameter specifies the maximum number of concurrent shared server processes. 
• SHARED_SERVER SESSIONS: This is the only option required for using shared servers and it specifies the maximum number of concurrent sessions for shared server connections. 

Actual parameters are variables or expressions that are referenced in a subprogram's parameter list. 

Consider the following method call, which has two real arguments named empno and amt: 

raise_sal(empno, amt);   

Variables defined in a subprogram specification and referenced in the subprogram body are known as formal parameters. 

The method that follows declares two formal parameters, empid and amt: 

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;   

Save Points are used to break a transaction into smaller parts. It allows you to roll back a portion of a transaction. Oracle Database allows for a maximum of 5 save points. When an error occurs, you can roll back to the point where the SAVEPOINT was stored.