Oracle Interview Questions and Answers

Oracle Interview Questions & Answers

Oracle is a secured database that is widely used in multinational companies. The frequently asked Oracle Interview Questions and Answers from the oracle database are given below.

1) What is a SYSTEM tablespace and when it is created?

When the database is created in the Oracle database system, it automatically generates a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary tables for the entire database.

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

Oracle version number refers:

• 9 – Major database release number
• 3 – Database maintenance release number
• 0 – Application server release number
• 5 – Component Specific release number
• 0 – Platform Specific release number

3) What is a bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle is used to import or export data from tables and views but it does not copy the structure of the same data.

The main advantage of BCP is a fast mechanism for copying data and you can also take the backup of data easily.

4) What is the relationship between database, tablespace, and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store the whole data of databases and each tablespace in the Oracle database consists of one or more files called datafiles. These data files are the physical structures that confirm the operating system in which Oracle is running.

5) How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:

1. library cache
2. data dictionary cache

6) What is the save point in the Oracle database?

Save points are used to divide a transaction into smaller parts. It allows the rolling back of a transaction. A maximum of five save points are allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

7) What is a hash cluster in Oracle?

Hash cluster is a technique to store data in the hash table and improve the performance of data retrieval. A hash function is applied to the table row’s cluster key value and store in the hash cluster.

Oracle Interview Questions and Answers

8) What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger, you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is issued.

Pre-query trigger fires before Pre-select trigger.

9) Explain the ANALYZE command in Oracle?

This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

• Analyze command is used to identify migrated and chained rows of the table or a cluster.
• It is used to validate the structure of an object.
• This helps in collecting the statistics about the object used by the user and is then stored on the data dictionary.
• It also helps in deleting statistics that are used by an object from the data dictionary.

10) Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

11) What is a BLOB data type in Oracle?

A BLOB data type is a data type with varying length binary string. It is used to store two gigabytes of memory. For BLOB data type, the length needs to be specified in bytes.

12) What is the difference between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a character by character while Replace is used to substitute a single character with a word.

13) Explain Temporal data types in Oracle

Oracle mainly provides the following temporal data types:

• Date Data Type: Different formats of Dates.
• TimeStamp Data Type: Has different formats of Time Stamp.
• Interval Data Type: Interval between dates and time.

14) What is a View?

A view is a logical table based on one or more tables or views. A View is also referred as a user-defined database object that is used to store the results of a SQL query, that can be referenced later in the course of time. Views do not store the data physically but as a virtual table, hence it can be referred as a logical table. The corresponding tables upon which the views are signified are called Base Tables and this doesn’t contain data.

Oracle Interview Questions and Answers

15) What are the different types of database objects?

A list of different types of database objects:

• Tables: This is a set of elements organized vertically and horizontally.
• Tablespaces: This is a logical storage unit in Oracle.
• Views: It is a virtual table derived from one or more tables.
• Indexes: This is a performance tuning method to process the records.
• Synonyms: This is a name for tables.

16) What is the usage of Save Points in the Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There is a maximum of 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to roll back from the point where the SAVEPOINT has been saved.

Oracle Interview Questions and Answers

17) What is the use of the ROWS option in the IMP command?

The ROWS option indicates whether the table rows should be imported.

18) What is a snapshot in the Oracle database?

A snapshot is a replica of a target master table from a single point in time. In simple words, you can say, a snapshot is a copy of a table on a remote database.

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

Hot backup (Online Backup): A hot backup is also known as an online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used 24 hours a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as an offline backup because it is done while the database has been shut down using the SHUTDOWN normal command. If the database is suddenly shut down with an uncertain condition it should be restarted with RESTRICT mode and then shut down with the NORMAL option.

For a complete cold backup, the following files must be backed up.

All data files, All control files, All online redo log files(optional), and the init.ora file (you can recreate it manually).

20) What is the use of the INDEXES option in the IMP command?

The INDEXES option is used to determine whether indexes are imported.

Oracle Interview Questions and Answers

21) What is the use of IGNORE option in the IMP command?

The IGNORE option is used to specify how object creation errors should be handled.

22) What is the use of the SHOW option in the IMP command?

The SHOW option specifies when the value of show=y, the DDL within the export file is displayed.

23) What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

• Form module
• Menu module
• Pl/SQL Library module
• Object Library module

24) What is the usage of ANALYZE command in Oracle?

ANALYZE command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

• It is used to identify migrated and chained rows of the table or cluster.
• It is used to validate the structure of the object.
• It helps in collecting the statistics about objects used by the optimizer. They are then stored in the data dictionary.
• It helps in deleting statistics used by objects from the data dictionary.

25) What is the use of FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

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

The to_char() function is used to convert a date to a character. You can also specify the format in which you want to output.

1. SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘YYYY-MM-DD’) FROM dual;  

Or,

1. SELECT to_char ( to_date (’12-12-2012′, ‘DD-MM-YYYY’) , ‘DD-MM-YYYY’) FROM dual;  

27) What types of joins are used in writing SUBQUERIES?

• Self-join
• Outer Join
• Equi-join

28) What is the usage of the control file in Oracle?

In Oracle, the control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an ORACLE database begins.

29) How do you store pictures in a database?

Yes, you can store pictures in a database using the Long Raw Data type. This data type is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type.

Oracle Interview Questions and Answers

30) What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.

Let’s see a procedure call that lists two actual parameters named empno and amt:

raise_sal(empno, amt);  

1. Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.

The following procedure declares two formal parameters named empid and amt:

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

31) What are the extensions used by Oracle reports?

Oracle reports are used to make businesses enable with the facility to provide information of all levels within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

32) How to convert a string to a date in the Oracle database?

Syntax: to_date (string , format)

Let us take an example :

1. to_date (‘2012-12-12’, ‘YYYY/MM/DD’)  

It will return on December 12, 2012.

33) How do you find the current date and time in Oracle?

The SYSDATE() function is used in Oracle to find the current date and time of the operating system on which the database is running.

1. SELECT TO_CHAR (SYSDATE, ‘MM-DD-YYYY HH24:MI: SS’) “Current_Date” FROM DUAL;  

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

1. SELECT TO_CHAR (SYSDATE, ‘YYYY-MM-DD HH24:MI: SS’) “Current_Date” FROM DUAL;  

Oracle Interview Questions and Answers

35) How will you differentiate between Varchar & Varchar2?

Both Varchar & Varchar2 are the Oracle data types that are used to store character strings of variable length. To point out the major differences between these,

Varchar : Can store characters up to 2000 bytes.
Varchar2: Can store characters up to 4000 bytes.
It will hold the space for characters defined during declaration even if all of them are not used
It will release the unused space

36) Describe an Oracle table

A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.
CREATE TABLE table_name

(

column1 datatype [ NULL | NOT NULL ],

column2 datatype [ NULL | NOT NULL ],

…

column_n datatype [ NULL | NOT NULL ]

);

Here,

table_name: This specifies the name of the table that you want to create.

column..n: It specifies the number of columns that you want to add to the table. Here, every column must have a datatype and should either be defined as “NULL” or “NOT NULL”. If in case, the value is left blank, it is treated as “NULL” as default.

37) Explain the relationship among database, tablespace, and data files?

An Oracle database possesses one or more logical storage units called tablespaces. Each tablespace in the Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. Talking about the data files, these are the physical structure that confirms with the operating system as to which Oracle program is running.

Oracle Interview Questions and Answers

38) What are the various Oracle database objects?

These are the Oracle Database Objects:

• Tables: This is a set of elements organized in a vertical and horizontal manner.
• Tablespaces: It is a logical storage unit in Oracle.
• Views: Views are virtual tables derived from one or more tables.
• Indexes: This is a performance tuning method to process the records.
• Synonyms: It is a name for tables.

39) What types of joins are used in writing subqueries?

A Join is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.

There are three types of joins in SQL that are used to write the subqueries.

Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key that references its own primary key.

Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.

Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

40) RAW datatype in Oracle

The RAW datatype in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table is 32767 bytes.

You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data is transferred to different systems. This data type can only be queried or can be inserted into a table.

Oracle Interview Questions and Answers

41) What is the use of Aggregate functions in Oracle?

An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations.

Some common Aggregate functions are:

• Average
• Count
• Sum

42) How to store pictures on to the database?

It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.

43) Where do you use DECODE and CASE Statements?

Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them. These functions are used in Oracle for data value transformation.

Example:

• Decode function
• Select OrderNum,
• DECODE (Status,’O’, ‘Ordered’,’P’, ‘Packed,’ S’,’ Shipped’, ’A’,’Arrived’)
• FROM Orders;
• Case function
• Select OrderNum
• , Case(When Status=’O’ then ‘Ordered’
• When Status =’P’ then Packed
• When Status=’S’ then ’Shipped’
• else ’Arrived’) end
• FROM Orders;
• Both these commands will display Order Numbers with their respective Statuses like this,
• Status O= Ordered
• Status P= Packed
• Status S= Shipped
• Status A= Arrived

Course Curriculum: MySQL DBA Certification Training

44) What do you mean by Merge in Oracle and how can you merge two tables?

A merge statement is used to merge the data from two tables subsequently. It selects the data from the source table and then inserts/updates it in the other table based on the condition provided in the query. It is also useful in data warehousing applications. 

45) What is the data type of the DUAL table?

The Dual table is basically a one-column table that is present in the Oracle database. This table has a single Varchar2(1) column called Dummy which has a value of ‘X’.

Oracle Interview Questions and Answers

46) Explain integrity constraints?

An integrity constraint is actually a declaration that is defined as a business rule for a table column. They are used to ensure the accuracy and consistency of data in the database. It can also be called a declarative way to define a business rule for a table’s column. There are a few types, namely:

• Domain Integrity
• Referential Integrity
• Domain Integrity

47) What is the difference between SQL and iSQL*Plus?

• SQL
• iSQL*Plus
• It is a language
• It is an environment
• Character and date columns heading are left-justified and number column headings are right-justified
• Default heading justification is in Centre
• Cannot be Abbreviated (short forms)
• Can be Abbreviated
• Does not have a continuation character
• Has a dash (-) as a continuation character if the command is longer than one line
• Use Functions to perform some formatting
• Use commands to format data

48) What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available. They are:
Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions are:

• Character
• Number
• Date
• Conversion
• General
• Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

• avg
• count
• max
• min
• sum
• stddev
• variance

49) Describe different types of General Function used in SQL?

General functions are of the following types:
NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then the NVL function returns the value of exp2.

NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)

NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)

COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternative values.

Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

50) What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:

• Select: Data Retrieval
• Insert, Update, Delete, Merge: Data Manipulation Language (DML)
• Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)
• Commit, Rollback, Savepoint: Transaction Control Statements
• Grant, Revoke: Data Control Language (DCL)

Oracle Interview Questions and Answers

FAQs about Oracle Interview Questions and Answers 

How hard is an Oracle interview?

The Prophet screening can be testing, however it’s not difficult to pass. The organization views employing in a serious way and is known for its thorough screening. This incorporates various rounds of meetings, each with an alternate concentration, from specialized information to social fit.

Is SQL harder than Oracle?

It’s moderately simple to learn — as long as you have a decent handle on Linux and SQL. On the off chance that you have previously learned SQL Server, you can unquestionably learn Prophet information bases. Prophet isn’t really more diligently to learn than Microsoft SQL Server — it’s simply unique.

What kind of database is Oracle?

Prophet Independent Data set is an across the board cloud data set answer for information stores, information lakes, functional revealing, and bunch information handling. Prophet utilizes AI to totally mechanize all standard information base assignments — guaranteeing better execution, dependability, security, and functional effectiveness.

Is Oracle is a programming language?

Prophet is clearly an article arranged social information base dispersed by the Prophet Company. You can make tables to fill records that can be recovered effectively by the programming language SQL (Organized Programming Language).

Does Oracle need coding?

To turn into a Prophet Data set Head, you needn’t bother with to be an accomplished individual in coding. Nonetheless, you ought to be know about SQL, Ruby On Rails, XML, C#, and Python dialects as an individual with this information is liked by Prophet Company.

Interested in Oracle Training Classes? Fill Your Details Here