Oracle Interview Questions and Answers: Mastering Database Management and SQL

Oracle Interview Questions and Answers: Mastering Database Management and SQL

 

Introduction : 

 

One of the largest international American companies in the field of computer technology, Oracle is headquartered in Austin, Texas. Oracle delivers corporate software, cloud-engineered solutions, database software and technologies, and database management systems under its own trademarks.

 

Oracle is the second-largest software corporation in terms of sales and market capitalization. The company is also renowned for creating tools for database development and middle tier software systems, including those for supply chain management (SCM), enterprise performance management (EPM), human capital management (HCM), and enterprise resource planning (ERP) software.

 

An Oracle is a hardware- or software-based system created to deliver a particular kind of data or service. An Oracle is a popular form of relational database management system (RDBMS) for managing and storing data in databases. Oracle Corporation created and sells it, and it’s renowned for its scalability, performance, and dependability. Numerous applications, such as data warehousing, business intelligence, and enterprise resource planning, can be carried out utilising Oracle databases (ERP). Oracle also provides a number of tools and technologies to support database management and upkeep for developers and administrators.

 

Oracle Interview Questions : 

• What are the components of physical database structure of Oracle database?

An Oracle database’s physical database structure has various components, including:

 

Datafiles: These are the physical files in which the database data is stored.

 

Control files: These files include information on the database’s physical structure, such as the names and locations of datafiles and redo log files.

 

Redo log files: These files include information that can be used to recover the database if it fails.

 

Online redo log files: These files save redo entries in real time, allowing the database to recover swiftly in the event of a failure.

 

Archive log files: These files contain a duplicate of the completed redo log files. They are employed for long-term recuperation.

 

Tablespaces are logical containers that hold database objects like tables, indexes, and clusters.

 

A segment is a collection of extents that contain data for a single type of object, such as a table or index.

 

Extents: An extent is a set of contiguous data blocks used to hold information for a certain segment.

 

Data blocks: In an Oracle database, these are the smallest units of data storage.

• What are the components of logical database structure in Oracle database?

Tablespaces, segments, and extents are examples of logical database structure components in an Oracle database.

 

In an Oracle database, a tablespace is a logical container for storing data. It is a method of organising data by grouping like objects together.

 

Within a tablespace, a segment is a logical container that contains data for a specific type of object, such as a table or index.

 

A segment’s extent is a group of contiguous data blocks that hold data for a given object. Extents are used to manage an object’s space allocation within a segment.

 

The physical data of the database is stored in datafiles.

 

Redo logs save the changes made to the database and can be used to recover the database if it fails.

 

Control files store database metadata such as the location of datafiles, redo logs, and other critical information required for the database to function.

• What is a tablespace?

A tablespace is a logical container in Oracle for holding database objects such as tables and indexes. A tablespace is essentially a layer between a database’s physical storage (e.g., hard discs) and logical storage (e.g., tables and indexes). Tablespaces enable more efficient database storage management by providing a logical way to arrange and access data.

• What is a SYSTEM tablespace and when it is created?

The SYSTEM tablespace is a built-in tablespace in Oracle that is created when a database is created. It contains the data dictionary tables, which store information on the structure and configuration of the database, such as information about tables, indexes, users, and other items. The SYSTEM tablespace also stores data for the sys and system users, which are created automatically when a database is built. These users have complete database access and are used for administrative duties.

• What is an Oracle table?

An Oracle table is a database object that is used to hold structured data within an Oracle Database. Tables are divided into columns and rows, with each column representing a different sort of data and each row representing a different record. SQL can be used to query, update, and change tables (Structured Query Language). Tables can also be linked to other tables using keys, resulting in a relational database structure.

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

Oracle 9.3.0.5.0’s version number is made up of four parts.

 

The major version number is 9, which denotes a significant release of the software.

 

The number 3 denotes a maintenance release, which contains bug fixes and other minor changes.

 

The application server release number is 0, signifying a release that contains updates exclusive to the software’s application server component.

 

The patchset number is 5.0, which indicates a release that includes a collection of patches and updates.

 

Each number symbolises a particular stage in the development of software. It also indicates which features and fixes are included in that particular version.

• What is bulk copy or BCP in Oracle?

The Bulk Copy Program (BCP) is an Oracle command-line utility that transfers data between an Oracle database and a data file, or between two Oracle databases. It is commonly used for high-speed data import and export activities, and it can run in parallel to improve performance. BCP supports a variety of data formats, such as CSV, fixed-width, and XML. It can also be used to replicate data from a query and conduct data transformations while doing so.

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

A database in Oracle is a collection of data files that store the actual structure of a database’s objects such as tables and indexes. A tablespace is a logical storage unit within a database that is used to organise related things together. Each tablespace is made up of one or more data files, which are the real physical disc files that store the data for the objects in the tablespace. In other words, a database is made up of one or more tablespaces, and each tablespace is made up of one or more data files.

• What is a snapshot in Oracle database?

A snapshot is a read-only copy of a table or tables from a certain point in time in an Oracle database. Snapshots can be used as the basis for a materialised view, which is a pre-computed join or aggregation of data from one or more tables, or for reporting or data analysis. Snapshots can be made manually or automatically, and they can be refreshed on a regular basis to keep them in sync with the original table or tables.

• How many memory layers are in the Oracle shared pool?

The Oracle database system stores data such as parsed SQL statements, execution plans, and session information in a shared pool within the SGA (System Global Area). The amount of memory tiers in the shared pool is determined by the version of Oracle used and the system settings. The shared pool is generally divided into numerous memory sections, such as the library cache and the data dictionary cache. However, no exact number of layers in the common pool is given.

For further information on the unique configuration of the shared pool in your system, see the Oracle documentation or a database administrator.

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

A hot backup is one that is taken while the database is still live and operating. This sort of backup keeps the database in a consistent state, which means that the data being backed up is coherent and easily restored. A hot backup has the advantage of allowing the database to continue functioning while the backup is being taken, minimising user disruption.

 

A cold backup, on the other hand, is one that is taken after the database has been shut down. Because the database is not functioning and cannot be modified during the backup process, a cold backup ensures that the data is in a consistent condition. However, the database must be shut down, which may cause substantial interruption to users.

 

In general, a hot backup is recommended since it causes less downtime and interruption to users, but a cold backup can be useful in specific circumstances, such as when a consistent state of the entire database is necessary or when the database is not working properly.

• What is save point in Oracle database?

In an Oracle database, a savepoint is a point within a transaction that allows you to roll back to that point without rolling back the entire transaction. This allows you to undo only a portion of the transaction as opposed to the complete transaction. When a transaction has numerous statements and you wish to undo only a portion of the transaction, savepoints can be handy. The SQL command “SAVEPOINT” can be used to set a savepoint, and the command “ROLLBACK TO SAVEPOINT” can be used to roll back to it.

• What is hash cluster in Oracle?

A hash cluster is a table storage structure in Oracle that stores rows of data depending on the result of applying a hash function to the values in one or more table columns, known as the cluster key. A hash cluster’s purpose is to improve query performance by allowing faster access to rows of data that have the same cluster key value. To determine the row’s storage location within the cluster, the cluster key is hashed. Hash clusters are often utilised for tables where the cluster key values are highly comparable.

• What are the various Oracle database objects?

Tables, views, indexes, sequences, procedures, functions, and triggers are examples of Oracle database objects. Each of these objects performs a distinct function and may be used to store and handle data in a variety of ways. Views provide a virtual table based on the result of a SELECT statement, indexes improve query performance by providing a faster way to look up data, sequences generate unique numbers, procedures and functions are used to encapsulate and reuse logic, and triggers are used to automatically execute actions in response to specific events.

• What is the difference between pre-select and pre-query?

The terms “pre-select” and “pre-query” have no technical implications in Oracle. However, the terms are typically used to indicate to the process of executing a query or picking certain data before a major operation is conducted. A developer, for example, might use a pre-select query to collect a group of data that would be used in a later action, like as a report or data analysis. Pre-select and pre-query are terms that allude to the idea of preparing or filtering data before it is used in a major job.

• What are the different types of modules in Oracle forms?

There are numerous sorts of modules that can be generated in Oracle Forms:

 

Form modules: These modules include the user interface elements that are displayed to the user, such as fields and buttons.

Menu modules: These are the modules that contain the menus and menu items that the user sees.

Library modules: These modules contain shared procedures and functions that other modules can use.

Object library modules: These are modules that contain reusable user interface components like fields and buttons that may be added to form modules.

PL/SQL library modules: These modules include PL/SQL code that other modules can use.

• What is the usage of ANALYZE command in Oracle?

In Oracle, the ANALYZE command is used to collect statistics on the contents of tables, indexes, and clusters. The optimizer then uses these statistics to make better informed recommendations about how to execute SQL statements. The ANALYZE command can be used to validate object structure, identify migrated and chained rows, and estimate storage requirements for a table or index. It can be used to gather statistics, validate an object’s structure, and identify chained and migrated rows, among other things.

• Can you create a synonym without having a table?

Yes, you can create a synonym in Oracle without having a table. A synonym is a database item that gives another database object, such as a table, view, or sequence, a different name. You can create a synonym for an object that already exists in another schema or database. To make a synonym, use the CREATE SYNONYM command and supply the synonym’s name as well as the name of the object it will reference. As an example:

 

CREATE SYNONYM my_synonym FOR other_schema.my_table;

 

This will generate a synonym called “my synonym,” which will refer to the table “my _table” in the “other _schema” schema.

 

It’s worth noting that if you create a synonym for an object that doesn’t exist, the statement will run without error, but the synonym will be invalid.

• What types of joins are used in writing SUBQUERIES?

Subqueries in Oracle can use the following joins: inner join, left outer join, right outer join, and full outer join. These joins can be used to aggregate data from various tables in a subquery, allowing you to extract only the information you require from the database.

• What is the usage of control file in Oracle?

In Oracle, a control file is a tiny binary file that holds crucial database information such as the names and locations of datafiles and redo log files, the database’s current status (e.g. open or closed), and the database’s unique name. The Oracle database management system uses the control file to start and open the database, as well as to recover the database in the event of a failure. It is an essential component of the Oracle database and should be backed up on a regular basis as part of a solid disaster recovery plan.

• What is a synonym?

A synonym is an alias for a database object, such as a table, view, sequence, or method, in the context of Oracle databases. Synonyms enable users to access the underlying object using an alternative name, which can be beneficial for simplifying SQL statements, concealing an item’s true location, or enabling backwards compatibility for changes to object names. SQL statements such as CREATE SYNONYM and DROP SYNONYM can be used to create and manage synonyms.

• What are the different types of synonyms?

There are numerous types of synonyms that can be produced in Oracle:

 

Private synonyms: Only the user who generated them has access to them.

 

Public synonyms: These are synonyms that are available to all users inside a schema.

 

Database link synonyms are used to access items in a distant database via a database link.

 

Synonyms for DBLINK: These synonyms are used to refer to a remote object on a database link.

 

Synonyms that are shared: These synonyms can be accessed by multiple schemas inside a database.

 

Global synonyms: In a distributed context, all users can access these synonyms across many databases.

• What is the usage of synonyms?

Synonyms are used in Oracle to provide a different name for a database object such as a table, view, or stored procedure. This can be useful for a variety of reasons, including:

 

Hiding an object’s true name and location from users but yet allowing them to use the thing.

 

Allowing many users to access the same object by the same name, even if the objects are in separate schemas.

 

Developing a clear and uniform naming strategy for objects used by numerous apps or teams.

 

The Oracle Data Dictionary or the SQL statement “Generate SYNONYM” can be used to create and manage synonyms.

 

In Oracle, here’s an example of how to make a synonym:

 

CREATE SYNONYM synonym_name FOR schema.table_name;

 

This creates a synonym called “synonym name” that points to the table “table _name” in the “schema” schema.

• How do you store pictures in a database?

The BLOB (Binary Large Object) data type in Oracle can be used to store images in a database. A BLOB is a variable-length binary string with a maximum size of 4 GB. To store a photo in a database, first convert it to a binary format, such as JPEG or PNG, and then insert it into the BLOB column of a database table. To insert or change BLOB data in the database, use the INSERT or UPDATE SQL statements.

 

The DBMS _LOB package can also be used to read, write, and manipulate BLOB data. To insert a BLOB into a table, first create an empty BLOB with the EMPTY _BLOB() function, then load data from a file into the BLOB with the BFILENAME function, and lastly insert the BLOB into the table with the INSERT command.

 

Example:

 

INSERT INTO images (id, image) VALUES (1, BFILENAME(‘IMAGE_DIR’, ‘image1.jpg’));

 

Where IMAGE _DIR is the directory containing the image file and image1.jpg is the file name.

• What is BLOB data type in Oracle?

BLOB (Binary Large OBject) is a data type in Oracle that can hold binary data up to 4 GB in size, such as photographs or other multimedia files. Large volumes of unstructured data, such as photos, audio, or video files, are often stored in BLOBs. The data is stored in binary format rather than character format, as with the VARCHAR and CHAR data types.

• What is the difference between TRANSLATE and REPLACE in Oracle?

The TRANSLATE function in Oracle is used to substitute one set of characters in a string with another set of characters. The following is the syntax:

 

TRANSLATE(string, old_characters, new_characters)

 

In contrast, the REPLACE function is used to replace a specified substring in a string with another one. The following is the syntax:

 

REPLACE(string, old_substring, new_substring)

 

In summary, the REPLACE function replaces a single substring while the TRANSLATE method changes many characters at once.

• What are the different types of database objects?

There are various sorts of database objects in Oracle, including:

 

Tables: Data is stored in rows and columns in tables.

 

Views: A virtual table constructed from the results of a SELECT operation.

 

Sequences: These are used to generate distinct numeric values.

 

Synonyms : These are used to give an object a different name.

 

Procedures: Database objects that contain a collection of PL/SQL statements.

 

Functions: Database objects that include a collection of PL/SQL statements that return a value.

 

Packages : These are groups of connected operations and functions.

Indexes: Used to improve query performance.

 

Constraints: Used to ensure the integrity of data.

 

Triggers : These are database items that are executed automatically in response to a certain event.

 

Materialized Views: Pre-calculate and save query results for speedier retrieval.

 

External tables: Allows data stored in flat files to be queried.

 

Kinds: These are user-defined data types that can be used to construct other database objects.

• What is the usage of Save Points in Oracle database?

In an Oracle database, a savepoint is a point within a transaction that allows you to roll back a portion of the transaction while keeping the remainder of the transaction intact. Savepoints are important when you only want to undo a section of a transaction rather than the complete transaction. They can also be used to divide large transactions into smaller, more manageable chunks. You can construct a savepoint with the SQL query “SAVEPOINT savepoint name.” To return to a previous savepoint, use the command “ROLLBACK TO savepoint _name.”

• What is the difference between post-database commit and post-form commit?

A “commit” command in Oracle is used to terminate a transaction and make all changes made during that transaction permanent in the database.

 

A “post-database commit” trigger is a sort of trigger that is triggered after a commit command is executed and the changes in the database have been made permanent. After the changes have been committed, this sort of trigger can be used to do other actions or activities, such as updating other tables or sending notifications.

 

A “post-form commit” trigger is one that is triggered after a commit command is entered into a form. After the changes in the form have been committed, this type of trigger can be used to do extra actions or activities, such as updating other form fields or displaying messages.

 

In summary, post-database commit triggers are active after a database transaction is committed, whereas post-form commit triggers are activated after a form transaction is committed.

• What is Logical backup in Oracle?

In Oracle, a logical backup is a backup of the database’s logical structure, such as table and index definitions, rather than the physical files that comprise the database. This sort of backup is usually performed with the export programme, which generates a file containing the SQL statements required to reconstruct the database structure and data. Logical backups can be used to move a database from one platform to another or to recover a database in the event of a disaster.

• What do you understand by Redo Log file mirroring?

Redo log file mirroring, commonly known as “mirroring the redo log,” is an Oracle function that copies the redo log files to a secondary disc or server. As modifications are made to the primary redo log files, this is done in real-time. If the primary redo log files fail due to a disc failure or other problem, the mirrored copies can be used to recover the database. This adds a layer of redundancy and protection to the database.

• What is the meaning of recursive hints in Oracle?

A “recursive hint” in Oracle refers to a sort of hint that can be used in a SQL query to instruct the database’s optimizer to apply a specific execution plan for a subquery that is referenced many times within the main query. This is useful when the optimizer does not select the most efficient plan for the subquery and the developer wishes to define how the subquery should be executed manually. In Oracle, put the keyword “RECURSIVE” directly before the subquery in the main query to use a recursive hint.

• What are the limitations of CHECK constraint?

The following are the restrictions of CHECK constraints in Oracle:

 

They can only be used to validate a single column of data.

They are unable to refer to other columns or tables.

They are unable to make use of user-defined functions or stored procedures.

They are unable to access session-specific data, such as the current date or user name.

They are unable to enforce sophisticated business rules involving several columns or tables.

Furthermore, CHECK restrictions are not implemented for data loaded via bulk load methods like SQL*Loader or external tables.

 

It should be noted that CHECK restrictions have a substantial performance impact on large tables and should be utilised with caution.

• What is the use of GRANT option in IMP command?

The GRANT option in Oracle’s IMP (Import) command is used to import object rights as well as the objects themselves from a dump file. This allows users to access the imported objects in the same way they did in the original database. Without the GRANT option, the objects would be imported with no associated privileges, rendering them inaccessible to formerly privileged users.

• What is the use of ROWS option in IMP command?

The ROWS option in Oracle’s IMP (Import) command specifies the number of rows to be imported at once. This is beneficial if the import process is causing the system to run out of memory, as providing a smaller number of rows to import at a time can help to reduce memory usage. Additionally, choosing the ROWS option can help to increase the import process’s performance.

• What is the use of INDEXES option in IMP command?

The INDEXES option in Oracle’s IMP (Import) command is used to import only the indexes of a database object rather than the full object. This can be beneficial in cases where only the indexes need to be regenerated, such as after a table has been exported and imported without its indexes. It can also be used to speed up the import process if a huge amount of data does not need to be imported.

• What is the use of IGNORE option in IMP command?

The IGNORE option in Oracle’s IMP (Import) command allows the import operation to continue even if errors occur. This indicates that any rows that create an error will be skipped, and the import will proceed to the next row. This can be handy if there are only a few problems in the data and you want to import as much of it as possible, rather than having the entire import process fail. It is crucial to note, however, that utilising the IGNORE option will not correct the problems in the data; rather, it will simply skip over them during the import process.

• What is the use of SHOW option in IMP command?

The SHOW option in Oracle’s IMP (Import) command displays the status of an import operation, including the number of rows imported for each table and the expected time remaining for the import to complete. This option is handy for monitoring the progress of a large import operation or addressing issues with an import that is taking longer than intended.

• What is the use of FILE param in IMP command?

The FILE parameter in Oracle’s IMP (Import) command specifies the location and name of the export file containing the data to be imported. Typically, the data in the export file is exported from another Oracle database using the EXP (Export) command. The FILE option specifies the location and name of the export file, allowing the IMP command to locate the data to import.

• How to convert a date to char in Oracle? Give one example.

To convert a date to a string in Oracle, use the TO _CHAR() function. Here’s an example of converting a date to a string in the ‘YYYY-MM-DD’ format:

 

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM DUAL;

 

SYSDATE is a built-in Oracle function that returns the current system date and time in this example. The format argument ‘YYYY-MM-DD’ indicates how the returned string should be formatted.

• What are actual and formal parameters?

An actual parameter in Oracle is a value or expression supplied to a method or function when it is called. A formal parameter is a variable or constant defined in the method or function definition that receives the value of the actual parameter.

 

For example, if a procedure called “myProc” accepts two arguments, “p_ param1” and “p _param2,” then when the procedure is run and the values “5” and “10” are supplied to it, “5” is the actual parameter for “p_ param1” and “10” is the actual parameter for “p param2”. The formal parameters would be “p _param1” and “p _param2” in the procedure definition.

• What are the extensions used by Oracle reports?

Oracle Reports supports the following file formats:

 

Report Definition File (.rdf): This file contains the layout and data source information for the report.

 

rep: A report file in binary format that provides the report layout and data source information.

 

jsp: Java Server Page, used for web-based reports.xml: XML formatted report data.

 

pdf, html, rtf, csv, and other formats: utilised for report output.

• How to convert a string to a date in Oracle database?

To convert a text to a date in Oracle, use the TO DATE() function. The first input is the string to be converted, and the second is the string’s format. To convert a text in the format ‘YYYY-MM-DD’ to a date, for example:

 

TO_DATE(‘2022-12-25’, ‘YYYY-MM-DD’)

 

By giving the right format mask, you can also use the TO _DATE() function to convert a string in a different format. To convert a string in the format ‘DD-MON-YY’ to a date, for example:

 

TO_DATE(’25-DEC-22′, ‘DD-MON-YY’)

 

It is critical to note that the format mask must match the format of the input string or an error will occur.

• How do you find current date and time in Oracle?

To find the current date and time in Oracle, use the “SYSDATE” function. This function returns the current system date and time in the database’s default date and time format.

 

To find the current date and time in Oracle, for example, execute the following query:

 

SELECT SYSDATE FROM DUAL;

 

This will return the current date and time in the format specified by the NLS_DATE_FORMAT session parameter or the database’s default format.

• What will be the syntax to find current date and time in format “YYYY-MM-DD”?

The syntax in Oracle to find the current date and time in the format “YYYY-MM-DD” is:

 

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM DUAL;

 

The SYSDATE function returns the current date and time, while the TO_ CHAR function converts it to the provided string format. The FROM DUAL is a dummy table in Oracle that is used to select a constant.

 

To truncate the time component and return only the date, use the TRUNC() function instead of TO _CHAR().

 

SELECT TRUNC(SYSDATE) FROM DUAL;