Oracle DB interview question for freshers/Oracle DB Interview Questions and Answers for Freshers & Experienced

What is a data dictionary and how can it be created?

Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

What is difference between Cartesian Join and Cross Join?

There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.

Cross join without where clause gives Cartesian product.

What is the fastest query method to fetch data from the table?

Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.

What is the difference between $ORACLE_BASE and $ORACLE_HOME?

Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.

What are the differences between LOV and List Item?

LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.

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 which lists two actual parameters named empno and amt:

raise_sal(empno, amt);
Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.

Following procedure declares two formal parameters named empid and amt:

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

What do you mean by a database transaction & what all TCL statements are available in Oracle?

Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements.

The set of statements include:

>> COMMIT: Used to make a transaction permanent.
>> ROLLBACK: Used to roll back the state of DB to last the commit point.
>> SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.

Can we store pictures in the database and if so, how it can be done?

Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various processes is known as recursive hint. Recursive hint is occurred because of the small size of data dictionary cache.

What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group. If the group fails, the database automatically switches over to the next group. It diminishes the performance.

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

The post-database commit trigger is executed after Oracle forms issue the commit to finalized transaction while, the post-form commit is fired during the post and commit transactions process, after the database commit occurs.

What is the usage of Save Points in Oracle database?

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

What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only accessed by the owner.

Public: It can be accessed by any database user.

What is the usage of control file in Oracle?

In Oracle, 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.

What is the use of Aggregate functions in Oracle?

Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are:

* AVG
* MIN
* MAX
* COUNT
* SUM
* STDEV

Why do we need integrity constraints in a database?

Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

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 object used by the optimizer. They are then stored in the data dictionary.
* It helps in deleting statistics used by object from the data dictionary.

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

How do we get field details of a table?

Describe <Table_Name> is used to get the field details of a specified table.

What is VArray?

VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.

What are temporal data types in Oracle?

Oracle provides following temporal data types:

* Date Data Type – Different formats of Dates
* TimeStamp Data Type – Different formats of Time Stamp
* Interval Data Type – Interval between dates and time

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 actually issued.

Pre-query trigger fires before Pre-select trigger.

What are the various Oracle database objects?

Tables: This is a set of elements organized in vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is 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.

What is hash cluster in Oracle?

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

How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:

1. library cache
2. data dictionary cache

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

Hot backup (Online Backup): A hot backup is also known as 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 for 24 hour a day, 7 days a week.

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

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

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

What is a snapshot in Oracle database?

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

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

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

What is 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 structure of same data.

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

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

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.

What is a sub query and what are the different types of subqueries?

Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.

There are two different types of subqueries:

* Correlated sub query

A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.

* Non-Correlated subquery

This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.

What is the difference between a Primary Key & a Unique Key?

Primary Key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

Given below are a few differences:

<> The primary key can be only one on the table while unique keys can be multiple.
<> The primary key cannot hold null value at all while the unique key allows multiple null values.
<> The primary key is a clustered index while a unique key is a non-clustered index.

What is an NVL function? How can it be used?

NVL is a function that helps the user to substitute value if null is encountered for an expression.

It can be used as the below syntax.

NVL (Value_In, Replace_With)

How does the ON-DELETE-CASCADE statement work?

Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

We can add ON DELETE CASCADE option on an existing table using the below set of commands.

Syntax:

ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES
PARENT_T1 (COLUMN1) ON DELETE CASCADE;

What is WITH CHECK OPTION?

The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

What is key preserved table?

A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.

What is USING Clause and give example?

The USING clause is used to specify with the column to test for equality when two tables are joined.

[sql]Select * from employee join salary using employee ID[/sql]

Employee tables join with the Salary tables with the Employee ID.

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.

What is BLOB datatype?

A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.

What is BLOB datatype?

Both the commands are used to remove data from the database.

Explain Temporal data types in Oracle

Oracle mainly provides these 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.

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

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 in 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 in a table.

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.

1. Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.
2. Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
3. 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.

Explain about 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 are then stored on to the data dictionary.
* It also helps in deleting statistics that are used by an object from the data dictionary.

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 a virtual table derived from one or more tables.
Indexes: This is a performance tuning method to process the records.
Synonyms: It is a name for tables.

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

The components of the logical database structure in Oracle database are:

* Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.

* Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.

What is Oracle and what are its different editions?

Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.

What is RAW datatype?

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

In which language Oracle has been developed?

Oracle has been developed using C Language.

Search
R4R Team
R4R provides Oracle DB Freshers questions and answers (Oracle DB Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,Oracle DB interview question for freshers,Oracle DB Freshers & Experienced Interview Questions and Answers,Oracle DB Objetive choice questions and answers,Oracle DB Multiple choice questions and answers,Oracle DB objective, Oracle DB questions , Oracle DB answers,Oracle DB MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for Oracle DB fresher interview questions ,Oracle DB Experienced interview questions,Oracle DB fresher interview questions and answers ,Oracle DB Experienced interview questions and answers,tricky Oracle DB queries for interview pdf,complex Oracle DB for practice with answers,Oracle DB for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .