Introduction

Found on 16 June 1977, in Santa Clara, California, USA, Oracle Corporation is a multinational tech company that seeks to provide a wide array of services ranging from Databases, Middleware, Applications, Enterprise management, Development software, File systems, Operating systems, Oracle Cloud so on and so forth. Founded by Larry Ellison, Bob Miner, and Ed Oates, Oracle was originally created to develop software that would work in coordination with IBM’s database products. While Oracle wasn’t the first company to provide relational database products, it was one of the first to do so. Other than IBM, Oracle stands out as one of the most innovative companies of the late 1970s and 1980s.

Oracle DBA Interview Question & Answers

1. Expand IBM and DBMS

IBM International Business Management; DBMS Database Management System

2. What are the ways to create a sample database?

We can create a database in two ways:

With Database Configuration Assistant (DBCA), a graphical tool.
Create a Database SQL statement.

3. What do you mean by DBMS?

DBMS or Database Management System is a series of programs and applications that allow you to create and maintain a database. DBMS assists the user for performing various operations such as inserting, deleting, updating, etc. into a database. Examples of popular DBMS systems are XML, Microsoft access.

4. What specific managers are offered while creating a database?

We get two options i.e. Enterprise Manager Database Express and Enterprise Manager Cloud Control. As we are creating the database in our local system, we should choose the default ‘Configure EM database Express’ option only.

5. Which language was put in use to develop the software?

Oracle has been developed in c language which garnered immense success to the corporation as it eased the process of porting data to different operating systems most of which supported c language.

6. What is the difference between RDBMS and DBMS?

RDBMS stands for Relational Database Management system whereas DBMS stands for Database management system. The primary difference lies in the way both the software programs store data. RDBMS stores large amounts of data in a table format whereas DBMS stores small quantities of data in a file format. Another key difference is in DBMS, data redundancy is common whereas in RDBMS redundancy of data can be reduced with the help of key and indexes in RDBMS.

7. Name a few popular RDBMS systems

Oracle, SQL Server from Microsoft, Db2 from IBM.

8. What makes a database ACID-compliant?

The presence of four properties — atomicity (integration of the entire database transaction, not just a component of it), consistency (means the only data which follows the appropriate validation rules is permitted to be written to the database), isolation (the ability to concurrently process multiple database transactions in a way that one does not affect another) and durability (means data is saved once a transaction is completed, even if a power outage or system failure occurs) — can ensure that a database transaction is completed in a timely manner.

9. Describe the role of the field, “the future period”?

The value tells how many periods are enterable when the users can enter their journal entries. The prerequisite, in this case, is that the future period should be in an open state. Good care should also be given to ensure that the users are not unintentionally entering the entries in a wrong period.

10. What is the integration advantage of oracle?

In addition to being ACID-compliant, Oracle Database is cross-platform. It can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux.

11. Mention a striking feature about oracle which garnered immense success?

Oracle is one of the first Databases that supported GNU/Linux in the late 1990s before GNU/Linux became a commerce product. It has been supporting this open platform since then.

12. What is the purpose of Oracle RMAN?

Oracle RMAN (recovery manager) is a software feature that allows oracle databases to automate backup and recovery. RMAN clients can conduct incremental backups, block media recovery, binary compression, encrypted backups, automated database duplication and cross-platform data conversion.

13. What is the primary function performed by Oracle Real Application Clusters (RAC)?

Oracle Real Application Clusters (RAC) ensures high availability that enables the system to be up and running without interruption of services in case one or more servers in a cluster fails.

14. What are the significant features of Oracle Expression Edition (XE)?

Expression Edition (XE) is a free-to-use version of the Oracle Database that is available on both Windows and GNU/Linux platforms. These are the features of Oracle Database XE 18c:
Limited to 2 CPUs
Can use the maximum of 2GB of RAM, and has 12GB of user data.
Very limited features

15. What do you mean by oracle marketing cloud?

Software as a Service platform where cloud is offered as the interface for automation of marketing and sales so that content creation and sales improvement can be monitored through the cloud platform itself is called Oracle Marketing Cloud.

16. How is oracle check constraint declared?

It can be declared in two ways. They are:

Column Level (In-Line) style:
It can be declared as part of the definition of an individual column or attribute.
Usually applied when the constraint is specific to that column only.
Table Level (Out of Line) style:
It can be declared as part of the table definition.
It can be declared on a combination of columns.

17. Mention a few restricted oracle check constraints ?

The constructs that cannot be included, are:

Queries to refer to values in other rows.
Calls to functions SYSDATE, UID, USER and USERENV.
The pseudo columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.

18. Mention the code for check constraint/ table level (out of line) style

Code : SQL >CREATE TABLE Mydept_2 (Deptno NUMBER (2),Dname VARCHAR2 (16),

CONSTRAINT Dep_chk_2 CHECK (Deptno in (10, 20, 30, 40)));

Output:

Explanation: The above Check statement creates a table Mydept_2 with CHECK CONSTRAINT but the constraint declared on Table level (out of line) style and the behaviour is the same as a column-level style declaration.

19. Cite a few parameters of INSERT Statement in Oracle

Below are the different parameters of INSERT Statement in Oracle:

table_name: The name of the table in which we want to insert the values.
(column1, column2, …… , column_n): columns in which we want to insert values
(expression1, expression2, expression3, expression_n): These are the values that we want to insert into the respective columns.
source_table: This is the table from where we will extract data to insert into the current table.
[where condition]: This condition is optional. It is used when we want to insert data based on some condition.

20. How does the UNIQUE Index work in Oracle?

A unique index is automatically created when we create a table that has a unique constraint but in case we want to explicitly create a UNIQUE index, we have to run the CREATE INDEX query. Once we execute the CREATE INDEX query with the particular column entry is provided in the database for all values assigned in the column or in other words indexes are created. When a UNIQUE INDEX is created in a column of a table, the index does not allow any other duplicate index to be created for that column because since the column of the table already has a Unique constraint associated with it, the unique constraint does not allow any duplicate values to be inserted for that column.

21. Name the mandatory components of RMAN

Target database: It is a database, which contains data files, controls files and redo files that are needed to be backed up or recovered. The target database control file is used by RMAN to collect metadata about the target database. Server sessions running on the target database perform all the backup and recovery work. It is a mandatory component for RMAN.

RMAN client: It is the client application that performs all the backup and recovery operations for the target database. It uses Oracle net to connect to the target database so that its location can be found on any host that is connected to the target host using Oracle Net. It is a command line interface which helps in issuing the backup, recovery, SQL and special RMAN commands. It is a mandatory component for RMAN.

22. Mention the key requirement for RMAN

One important requirement for RMAN is that it can be used for Oracle database version 8.0 and greater.

23. What are the available wildcards in Oracle?

The available WILDCARDS in Oracle are:

% : Used to present any sequence of zero or more character
Represents any single character, only at that position

24. Tell me about Oracle decode function

Oracle provides a decode function to the user in which we add procedural if – then – else to the specified query. In the decode function it compares the expression value with each search value one by one. If expression is equal to the search value then it returns the results that correspond to the oracle database. if the expression value does not match with the search value at that time it returns the default value. In another situation if the default value is omitted at that time oracle returns the null value. The decode function is suitable for Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.

25. Name a few character data types in oracle

char(size): It is used to store fixed length character strings. It has a maximum size of 2000 bytes.
varchar2(size): It is used to store variable length character strings. It has a maximum size from 1byte to 4KB.
nvarchar2(size): It is used to store variable length Unicode character strings. The upper limit is 4000 bytes.
long: It is also used to store variable length string and it is backward compatible. It can be used to store up to 2 gigabytes.
raw: It is variable length binary strings. It has a maximum size of 2000 bytes.
long raw: It is variable length binary strings and it is backward compatible. It has a maximum size of 2GB.

26. What do you mean by PL/SQL?

PL/SQL is a procedural language extension over SQL provided by Oracle. It facilitates declaration of variables, functions, and conditional operators in SQL syntax thereby giving the developer more freedom and ease to design complex queries.

27. Expand and explain BLOB

BLOB is a binary large object data type. It is used to store unstructured data such as video, audio or image. Maximum capacity of a BLOB is 4GB-1 in Oracle.

28. What purpose is served by The result offset and fetch first Clauses?

The result offset clause is used to skip the N first rows and select the remaining rows as a result set. The fetch first clause is used along with the result offset clause to limit the number of rows selected in the result set.

29. Explain Oracle COALESCE. What mechanism does it work on?

An oracle COALESCE is nothing but a function that is used to get the first, not null value in the given argument in the COALESCE function. This function accepts multiple arguments and evaluates it until it finds the first, not null value. If it does not find any non-null value then return NULL. The COALESCE function works on the IF THEN ELSE mechanism.

30. What do you comprehend by Bundles of PL/SQL?

PL/SQL bundles are composition protests that gather capacities, put away techniques, and cursor additional factors at one position. Bundles have two important segments.

Package Specifications

Package body

31. How does the LGWR process write information?

The LGWR process writes information into Redolog files.

32. Mention the file which maintains the overall structure of databases

control file

33. Which method is better to validate data in a table.. INTEGRITY CONSTRAINT or STORED PROCEDURE?

integrity constraint is a better option as it gets automatically checked while data is inserted into or update in a table while a stored procedure has to be specifically invoked

34. What type of trigger is a POST-BLOCK trigger?

Navigational trigger

35. Mention the crucial parts of a FORM

blocks, fields, pages, triggers and form level procedures

36. What types of files are created when you operate forms 3.0?

INP and FRM

37. Mention the platforms where we can successfully download oracle

linux, Windows and MAC

38. What distinguishes OracleV3 and OracleV4?

Oracle V3 was the second launch in the year 1983 and OracleV4 was launched in the market in 1984 and had an additional feature of being compatible with the MS-DOS operating system developed by Microsoft

39. Name a few categories of oracle operators

Unary and binary operators, arithmetic operators, set operators, logical operators so on and so forth

40. What is the format for comparison operators in the WHERE clause?

The format of the operator in the WHERE clause is WHERE expression OPERATOR VALUE

41. What are the different types of logical operators provided by oracle?

AND- Logical conjunction operator
OR- logical disjunction operator
NOT- logical negation operator

42. What should be kept in mind while dealing with financial data in oracle?

The important thing in financial data means account balance must store numeric or decimal type value.

43. In oracle, what purpose does Int alias serve?

it is equivalent to number (38) in numeric data type

44. Why do we use Rollback segments in the Oracle database?

They allow the database to maintain read consistency between multiple tractions

45. What do you mean by Hit Ratio?

It is a measure of how well the data cache buffer is handling requests for data. Hit Ratio= (logical reads-physical reads- hit misses)/logical reads.

46. Cite the function served by DENSE_RANK

It is a type of oracle analytic function that calculates the rank of a row as consecutive integers.

47. Which area of PGA stores binding variables and run-time buffers information?

Private SQL area

48. Examine the significance of data dictionary

The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database’s SYSTEM tablespace.

49. Expand SGA and name it’s elements

SGA stands for System Global Area and it contains shared pool, database buffer cache, java pool, redo log buffer.

50. What SYSTEM VARIABLE is used to refer to DATABASE TIME?

$$dbtime$$

Oracle Interview Questions and Answers