Easy way to IT Job

Oracle Interview Questions and Answers

Oracle Interview Questions and Answers

Published On: March 8, 2022

Oracle is the first and most popular database designed for enterprise grid computing, and it gives effective ways to manage information and applications of an organization. This is RDBMS (Relational Database Management System) used to implement OOPs-based features for the applications.

Oracle Professionals are in high demand in the industries to handle data for communication and services. It is your time to check your learning process through our Oracle Interview Questions and Answers to ace the technical rounds easily.

1. What are the various components of Oracle’s physical database structure?

There are three major components in the physical database structure of Oracle and they are

  • Greater than equal to two redo log files.
  • Greater than equal to one data file
  • Greater than equal to one control file.

2. Define the logical structure of Oracle.

The Oracle Database allocates logical space for all data in the database to the physical files, and the logical units of database space allocation are data blocks, segments, tablespaces, and extents. At the physical level, the data is stored in data files on a local disk. The data in the data files are stored in operating system blocks.


3. What are the main components of the logical database structure in the Oracle database?

Tablespaces and Schema Objects are the two main components in the logical structure of the Oracle database.

  • Tablespaces are the fundamental storage allocation in an Oracle database, and each tablespace is compiled of one or more physical files that are in the operating system. The SYSTEM tablespace is used to develop each and every database, and other tablespaces are created by DBA (Database Administrator).
  • Database’s Schema Objects means that a schema in Oracle is the same as to the account or username. Every object in the database is owned by a schema, and every oracle database is created with two initial schemas. SYS is used to store the data dictionary, and SYSTEM, which often stores some data dictionary extensions and critical tables for other tools. Other schemas are created by the DBA, and each schema can be granted quotas in any tablespace.

4. What are the default tablespaces of Oracle?

The default tablespaces of Oracle are below

  • The SYSTEM and SYSAUX tablespaces for storing system-generated objects like data dictionary tables. Users can’t store any object in these tablespaces.
  • The USERS tablespace is used to store ad-hoc queries
  • The UNDOTBS1 tablespace is used to hold the undo data
  • The TEMP tablespace is the temporary tablespace used to store intermediate results of sorting, hashing, and large object processing operations.

5. How to start a listener in Oracle?

The Oracle Listener is started when the Server Machine of Oracle restarted. If the listener gets stopped, it will be restarted through the command line. To start the listener the following command will be executed.

  • For Windows: LSNRCTL.EXE start
  • For Solaris: lsnrctl START

6. How will you drop constraint in Oracle?

The constraint will be dropped in Oracle using the following steps.

  • Using the Alter table command on the table where the constraints exist by the command of ALTER TABLE table_name;
  • Using the Drop command on the constraint by mentioning the constraint name with the following command

DROP CONSTRAINT name_of_constraint


7. Define Oracle Golden Gate.

The Oracle Golden Gate 12 C is a replication and Change Data Capture (CDC) software package which is used to perform data integration in real-time, transformation, configuration management, transactional change data capture, enhanced security, and system verification between enterprise and operational systems.


8. How will you rename a column in Oracle?

We can rename the column in Oracle using the following commands:

  • Using the Alter Table Command on the table where the column exist by the command

ALTER TABLE table_name

  • Using the RENAME command on the column that requires to be changed by the command

RENAME old_col_name to new_col_name


9. Explain SGA in Oracle

The SGA (System Global Area) is a set of shared memory structures that are shared by the processes that belong to a database instance. They have control information and data required for one database instance.


10. Define Subquery in Oracle

A subquery is a query that is a part of another query and they will be created within SQL statements using commands like From, Where, and Select. The queries inside from clause of a Select statement is known as an inline view. When it is inside a Where clause, it is known as a nested query.


11. Define Dense_Rank in Oracle

The Dense_Rank function of Oracle is used to find the rank of a particular row from a group of rows. This function will return consecutive rankings and it can be used as an analytic or an aggregate function. For instance, the syntax for the function used as an analytic function should be as follows.

DENSE_RANK (expression1, expression2, …) WITHIN GROUP (ORDER BY expression1, expression2, ….)


12. Explain the memory layers of Oracle Shared Pool

There are two memory layers in the Oracle Shared Pool

  • Library Cache: The information of the SQL statements that were parsed, data about cursors, and any plain data that may be present in the Library Cache.
  • Data Dictionary Cache: The Data Dictionary Cache Layer consists of the information of the user accounts, privileges, and information of segments.

13. What are the various database objects in Oracle?

Following are the database objects in Oracle

  • Tables that are set of elements organized in a vertical and horizontal manner
  • Tablespaces are the logical storage unit in Oracle
  • Views are defined as a virtual table that derives from one or multiple tables
  • Indexes are a performance tuning method to process the records
  • Synonyms are the names of tables

14. What is the use of ANALYZE command in Oracle?

The ANALYZE command in Oracle is used to perform functions on indexes, tables, or clusters. Following are the sample use cases of ANALYZE command in Oracle

  • The ANALYZE command is used to find out migrated and chained rows of the table or cluster. It is also used to validate the structure of the object.
  • The ANALYZE command helps in gathering the statistics of objects that are used by the optimizer for storing them in the data dictionary.
  • The ANALYZE command helps in removing statistics used by objects from the data dictionary.

15. What are the various types of Synonyms in the Oracle database?

Synonyms are described as tables, views, and sequences or program units, and there are two types of synonyms in the Oracle database as follows.

  • Private Synonyms: They are only accessible to the owner
  • Public Synonyms: They are accessible to any database user.

16. Define Logical Backup in Oracle

The logical backup is the mechanism used for reading the database records and writing them into a file. It can be used as an Export Utility to take the backup while Import Utility used to recover from the backup.


17. Which data type has a varying length binary string in Oracle?

The BLOB data type in the Oracle has a varying length binary string and it is used for storing two gigabytes of memory and the length requires to be specified in bytes. To illustrate the usage of the BLOB data type, the following example is used.

Creating a table:

create table photos(name varchar(32) not null primary key, picture blob(10M));

Querying for all logotype pictures

select name, length(picture) from photos where name like ‘%logo%’;


18. What are the uses of GRANT, ROWS, IGNORE, and INDEXES options in the IMP command?

  • The GRANT option in the IMP command is used for importing object grants.
  • The ROWS option in the IMP command is used for indicating whether the table rows should be imported or not.
  • The IGNORE option in the IMP command is used to understand how object creation errors should be handled.
  • The INDEXES option in the IMP command is used to determine whether indexes are imported or not.

19. Describe actual parameters and formal parameters with examples

Actual Parameters are defined as the variables or expressions referenced in the parameter list of a subprogram. The example of a procedure call that lists two actual parameters named empNumber and amount is as follows.

updateSalary (empNumber, amount);

Formal Parameters are defined as variables declared in a subprogram specification and referenced in the subprogram body. The example of a procedure that declares two formal parameters named empID and the amount is as follows

PROCEDURE updateSalary(empID Integer, amount REAL) IS currentSalary REAL;


20. Write a query to list the duplicate values in an Oracle Table.

To list the duplicate values in an Oracle table, the following command should be used.

SELECT NAME, COUNT (NAME) FROM EMPLOYEE GROUP BY NAME HAVING COUNT (NAME) > 1;


21. How to use the TRANSLATE function?

The TRANSLATE function of oracle is used to replace a sequence of characters in a string with some other set of characters. The TRANSLATE function is used to replace a single character at a time. To translate the string “EMPID13” to “FRED13”, the following command should be used.

TRANSLATE(“EMPID13”, “EMPID”, “FRED”);


22. How will you find the average salary of employees from the EMP Oracle Table?

To find the average salary of employees from the EMP table using Oracle query, the following is used.

SELECT AVG(SALARY) FROM EMP GROUP BY SALARY;


23. How to convert a string to a date in Oracle?

To convert a string to a date, the to_date command will be used as follows

SELECT to_date (‘2022-02-21’, ‘YYYY/MM/DD’) FROM dual;


24. List some of the common types of modules in Oracle Forms

The most common types of modules in Oracle Forms are below

  • Form Module
  • Menu Module
  • PL/SQL Library Module
  • Object Library Module

25. Define Oracle Apex

The Oracle Apex is also known as the Oracle Application Express which is an application environment tool used for creating, designing, and executing database-driven responsive applications. It requires fundamental programming knowledge to develop applications on this web-based tool, and it can be executed on any device like mobile or desktops.


Conclusion

Learning Oracle with hands-on exposure helps you ace the interviews easily. Enroll at Softlogic for the best Oracle Training in Chennai as we offer the course with a comprehensive curriculum that contains satisfying hands-on experiences, case studies, and project practices on a real-time industry database. Earn a specialized certification in Oracle Database Management in our Oracle Training Institute in Chennai.

Just a minute!

If you have any questions that you did not find answers for, our counsellors are here to answer them. You can get all your queries answered before deciding to join SLA and move your career forward.

We are excited to get started with you

Give us your information and we will arange for a free call (at your convenience) with one of our counsellors. You can get all your queries answered before deciding to join SLA and move your career forward.